https://kotlinlang.org logo
#exposed
Title
# exposed
a

Alexander Weickmann

03/23/2020, 1:35 PM
Hi all, I am trying to figure out how Isolation Level "SERIALIZABLE" works. Here's what I want to achieve:
Copy code
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:
Copy code
withContext(<http://Dispatchers.IO|Dispatchers.IO>) {
            println(TransactionManager.manager.defaultIsolationLevel) // prints 8 = SERIALIZABLE
            transaction {
                MyDao.new { ... }
            }
        }
During setup, I have:
Copy code
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 ...
s

spand

03/23/2020, 1:41 PM
Can you get the correct result if you connect to the db and run the sql directly ?
a

Alexander Weickmann

03/23/2020, 1:55 PM
@spand good tip. I tried it and it actually works how I expected:
Copy code
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:
Copy code
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:
Copy code
select count(*) from MY_TABLE;
 count 
-------
 25874
(1 row)
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
🤘 1
s

spand

03/23/2020, 2:36 PM
Good to know!
11 Views