Alexander Weickmann
03/23/2020, 1:35 PMwithContext(<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
03/23/2020, 1:41 PMAlexander Weickmann
03/23/2020, 1:55 PMstart 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)
spand
03/23/2020, 2:36 PM