Will a future update of hsqldb bring SSI in the mvcc model, or is it already decided it will not be done?
I like mvcc's flexibility and performance, but it permits unserializable transactions. Mvlocks on the other hand properly rollbacks unserializable transactions, but the locking obviously has disadvantages, in my case especially in tests with which I verify database behavior with threads that need to sync to each other (which creates a deadlock).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I run it with tx_level=serializable. The doc says that serializable is translated to snapshot isolation under mvcc.
I have a db test that sets up a table with two records containing 10 and 20. It then starts 2 threads, that simultaneously sum the records and insert their sum into the same table.
Under mvlocks, the first thread gets the sum of 30 and inserts it, while the second thread gets the same sum and wants to insert it, but hsqldb throws a serializability error. If my framework then retries the second thread, it gets the new sum of 60 (the first thread's 30 is included) and succeeds. This is the desired and expected behavior.
Running the same test under mvcc shows both threads succesfully inserting the sum of 30, so that the records are 10, 20, 30, 30. This is the expected, normal behavior of non-serializable snapshot isolation.
I'd like serializable snapshot isolation to be added, which would force the second sum to be 60. A google search shows that such a thing exists, and is implemented in postgres...
Last edit: Marnick L'Eau 2018-01-06
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Will a future update of hsqldb bring SSI in the mvcc model, or is it already decided it will not be done?
I like mvcc's flexibility and performance, but it permits unserializable transactions. Mvlocks on the other hand properly rollbacks unserializable transactions, but the locking obviously has disadvantages, in my case especially in tests with which I verify database behavior with threads that need to sync to each other (which creates a deadlock).
Have you tried running in MVCC with REPEATABLE READS or SERIALIZABLE transaction isolation level? If so, what behaviour are you concerned about?
I run it with tx_level=serializable. The doc says that serializable is translated to snapshot isolation under mvcc.
I have a db test that sets up a table with two records containing 10 and 20. It then starts 2 threads, that simultaneously sum the records and insert their sum into the same table.
Under mvlocks, the first thread gets the sum of 30 and inserts it, while the second thread gets the same sum and wants to insert it, but hsqldb throws a serializability error. If my framework then retries the second thread, it gets the new sum of 60 (the first thread's 30 is included) and succeeds. This is the desired and expected behavior.
Running the same test under mvcc shows both threads succesfully inserting the sum of 30, so that the records are 10, 20, 30, 30. This is the expected, normal behavior of non-serializable snapshot isolation.
I'd like serializable snapshot isolation to be added, which would force the second sum to be 60. A google search shows that such a thing exists, and is implemented in postgres...
Last edit: Marnick L'Eau 2018-01-06
http://jdbi.org/#_serializable_transactions
I checked the PostgreSQL paper. It's something relatively new. We can certainly implement it but I don't have a time frame at the moment.