MVCC and the isolation of update transactions
70 views
Skip to first unread message
Pavol Mederly
Apr 19, 2012, 7:16:13 AM4/19/12
to h2-da...@googlegroups.com
Hello,
please, how exactly does the transaction isolation work when using MVCC? I.e. how to ensure that two update transactions are serialized, if possible, or one of them is rolled back (if they are not serializable)?
As an example, let us take a table with three columns: ID, A, B and one row (1, "A0", "B0").
I have two threads. The first is modifying attribute A (successively changing it to A1, A2, ...), second does the same to attribute B (changing it to B1, B2, ...).
However, each of the threads reads both attributes and writes new value of "its" attribute and old value of the other. (This is an abstraction of a functioning of a bigger product that does the same to objects with tens of attributes.)
When using MVCC=FALSE, LOCK_MODE=1, a deadlock is correctly detected.
However, when using MVCC=TRUE, no deadlocks are seen and the later commit overwrites values stored by the preceding one. (I would expect that one of the threads would be disallowed to commit, because such transactions are not serializable.)
The situation looks like this:
[Modifier for first attribute] --- Iteration number 1 for Modifier for first attribute ---
[Modifier for second attribute] --- Iteration number 1 for Modifier for second attribute ---
[Modifier for second attribute] Read: attribute A = A0, attribute B = B0 [my value = B0]
[Modifier for first attribute] Read: attribute A = A0, attribute B = B0 [my value = A0]
[Modifier for second attribute] UPDATE TEST SET A = 'A0', B = 'B1' WHERE ID = 1 [my value = B1]
[Modifier for first attribute] UPDATE TEST SET A = 'A1', B = 'B0' WHERE ID = 1 [my value = A1]
[Modifier for second attribute] Committed (iteration number 1)
[Modifier for second attribute] --- Iteration number 2 for Modifier for second attribute ---
[Modifier for second attribute] Read: attribute A = A1, attribute B = B0 [my value = B0]
[Modifier for second attribute] Got exception: java.lang.RuntimeException: Got B0, expected B1
(This is my exception signalling that the value of "B" attribute was not as expected. The new value (B1) was overwritten by the original value (B0) by the first thread.)
Full source as well as the log is in the attachment.
Perhaps I'm missing some switch to enable row-level locking? Or some stupid mistake?
Thank you very much.
Pavol Mederly
please, how exactly does the transaction isolation work when using MVCC? I.e. how to ensure that two update transactions are serialized, if possible, or one of them is rolled back (if they are not serializable)?
As an example, let us take a table with three columns: ID, A, B and one row (1, "A0", "B0").
I have two threads. The first is modifying attribute A (successively changing it to A1, A2, ...), second does the same to attribute B (changing it to B1, B2, ...).
However, each of the threads reads both attributes and writes new value of "its" attribute and old value of the other. (This is an abstraction of a functioning of a bigger product that does the same to objects with tens of attributes.)
When using MVCC=FALSE, LOCK_MODE=1, a deadlock is correctly detected.
However, when using MVCC=TRUE, no deadlocks are seen and the later commit overwrites values stored by the preceding one. (I would expect that one of the threads would be disallowed to commit, because such transactions are not serializable.)
The situation looks like this:
[Modifier for first attribute] --- Iteration number 1 for Modifier for first attribute ---
[Modifier for second attribute] --- Iteration number 1 for Modifier for second attribute ---
[Modifier for second attribute] Read: attribute A = A0, attribute B = B0 [my value = B0]
[Modifier for first attribute] Read: attribute A = A0, attribute B = B0 [my value = A0]
[Modifier for second attribute] UPDATE TEST SET A = 'A0', B = 'B1' WHERE ID = 1 [my value = B1]
[Modifier for first attribute] UPDATE TEST SET A = 'A1', B = 'B0' WHERE ID = 1 [my value = A1]
[Modifier for second attribute] Committed (iteration number 1)
[Modifier for second attribute] --- Iteration number 2 for Modifier for second attribute ---
[Modifier for second attribute] Read: attribute A = A1, attribute B = B0 [my value = B0]
[Modifier for second attribute] Got exception: java.lang.RuntimeException: Got B0, expected B1
(This is my exception signalling that the value of "B" attribute was not as expected. The new value (B1) was overwritten by the original value (B0) by the first thread.)
Full source as well as the log is in the attachment.
Perhaps I'm missing some switch to enable row-level locking? Or some stupid mistake?
Thank you very much.
Pavol Mederly
Peter Yuill
Apr 20, 2012, 6:34:33 PM4/20/12
to h2-da...@googlegroups.com
Hi Pavol,
> Full source as well as the log is in the attachment.
>
> Perhaps I'm missing some switch to enable row-level locking? Or some
> stupid mistake?
SELECT ... FOR UPDATE is the simple way to achieve locking in either
MVCC mode (selected rows) or non MVCC mode (table).
http://www.h2database.com/html/grammar.html#select
Regards,
Peter
Reply all
Reply to author
Forward
0 new messages