Hi all, I am trying to figure out how Isolation Le...

# exposed

[Alexander Weickmann]

Hi all,
I am trying to figure out how Isolation Level "SERIALIZABLE" works. Here's what I want to achieve:

withContext(<http://Dispatchers.IO|Dispatchers.IO>) {
            println(TransactionManager.manager.defaultIsolationLevel) // prints 8 = SERIALIZABLE
            transaction {
                val countBefore = MyDao.find(Op.TRUE).count()
                println(countBefore)
                println("SLEEP")
                Thread.sleep(10000)
                println("UPDATE")
                val countUpdate = MyDao.find(Op.TRUE).count()
                println(countUpdate) // EXPECTING TO BE SAME AS countBefore
            }
        }

Now I have a separate query that inserts new rows into MyDao, and I trigger it during the 10 seconds of sleep of the first transaction:

withContext(<http://Dispatchers.IO|Dispatchers.IO>) {
            println(TransactionManager.manager.defaultIsolationLevel) // prints 8 = SERIALIZABLE
            transaction {
                MyDao.new { ... }
            }
        }

During setup, I have:

TransactionManager.manager.defaultIsolationLevel = Connection.TRANSACTION_SERIALIZABLE

What I expected is that the table behind MyDao gets locked for the time the first transaction is still running. But this is not the case and the first transaction prints 2 different counts as a result ...

[spand]

Can you get the correct result if you connect to the db and run the sql directly ?

[Alexander Weickmann]

@spand good tip. I tried it and it actually works how I expected:

start transaction isolation level serializable; select count(*) from MY_TABLE; select pg_sleep(5); select count(*) from MY_TABLE; commit;
START TRANSACTION
 count 
-------
 25873
(1 row)

 pg_sleep 
----------
 
(1 row)

 count 
-------
 25873
(1 row)

while during the sleep I do in parallel:

start transaction isolation level serializable; INSERT INTO MY_TABLE (...) VALUES (...); commit;
START TRANSACTION
INSERT 0 1
COMMIT

The second transaction completes during the sleep of the first and the result of the second select statement is unaffected.

Just to verify that the second transaction is effective, if I do another select afterwards, I get the correct modified result:

select count(*) from MY_TABLE;
 count 
-------
 25874
(1 row)

[Alexander Weickmann]

I tried upgrading to the latest exposed version. as we are still on 0.16.2 it's quite old by now. and indeed: after the update to 0.22.1, it works as expected now

[spand]

Good to know!