Newbie here, probably a very navie question I am asking.
Can anyone kindly explain isolation levels in a simple way (targeting end users who do not necessarily have the knowledge to comprehend the inner working) with examples?
I have attempted the following, with a cockroachdb docker instance
create table test (i int); insert into test values (1), (2);
In terminal 1, I issued:
begin; insert into test values (3); -- enter twice without committing
INSERT 1 root@26257/test OPEN>_
In terminal 2, I issued
insert into test values (4);
As expected, the statement ran successfully with a response:
In terminal 2 again, I issued
select * from test;
The terminal 2 got stuck, nothing happened, for a long time…
After 15 mins of time, back to terminal 1, I issued
Terminal 2 became responsive again.
Is this the expected behaviour of the default transaction isolation level
SERIALIZABLE. I am also confused with the term
LOCKLESS mentioned in the blog. It appears to me that if the client (terminal 1) issues a transaction statement
begin without committing, the whole
table test gets locked for
select by the other client (terminal 2). This can be a problem if a client is misconfigured, issues a
commit, or the client takes a long time to commit. In these scenario, other clients need to wait for a long time.
It would be great if someone can help explaining this, with examples possibly, and solutions to mitigate clients.