Transaction isolation levels explaination

Newbie here, probably a very navie question I am asking.

I am trying to understand transaction isolation levels: SERIALIZABLE vs SNAPSHOT
I have also read the blog page, but still doesn’t quite get it.

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

I got

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: INSERT 1
In terminal 2 again, I issued

select * from test;

The terminal 2 got stuck, nothing happened, for a long time…
After 15 mins of :tea: time, back to terminal 1, I issued

commit;

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 begin without 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 :turtle: clients.

To better understand the transaction isolation levels, have a look at this video: https://www.youtube.com/watch?v=-7VjPVmw4vM&t=16m23s

The marble example is a good illustration of what serialisable offers over snapshot.

Both isolation levels are lockless in CDB which means if there is contention, your tx will fail instantly and you will be given the opportunity to retry: https://www.cockroachlabs.com/docs/transactions.html#client-side-intervention

As for the terminal locking, my guess is it is retying in the background and lock the cli until the tx succeed.

1 Like

Cockroach’s transactions are now something of a hybrid between lockless and lock-based models. Where possible, we will wait for a competing transaction to finish instead of forcing an abort and restart (although we’re still somewhat more likely to force a transaction to restart compared to other databases, which generally only do this for deadlocks)

In all transactional systems, it’s important to keep your read/write transactions short if there is anything else going on; typing them interactively at a terminal is likely to be disruptive. Even in a lockless system, a transaction that begins and waits a long time before committing or rolling back will either cause other transactions to get aborted or be aborted itself. (read-only transactions are safe to leave open for a long time)

1 Like