Ioana Danes wrote:
I am assuming that in the original scenario T3 -
insert into Table1 is locked by T2 - update on Table2.
So an insert into a table is locked by an update on
another table??? Sorry but this it does not make any
sense for me...
Here's a snapshot of what happens inside Sequoia when your use case deadlocks.
Table locking queues:
table1 lock queue | table2 lock queue
------------------|------------------
T2 - HOLDS LOCK | T3 - HOLDS LOCK
T3 | T2
Information in these queues is used to route requests to the execution queues.
A request impacting a table for which the transaction does not hold the lock
is added to the conflicting queue. Otherwise the request goes to the non-conflicting queue.
Request execution queues:
Non-conflicting queue | Conflicting queue
-----------------------------------|-----------------------------------
T2 (insert into table1) - EXECUTED | T3 (insert into table1) - EXECUTED
T3 (update table2) - EXECUTED | T2 (update table2) - BLOCKED IN DB
| T3 (insert into table1) - BLOCKED BEHIND
T2 (update table2)
Request "T3 (insert into table1)" waits for "T2 (update table2)" to execute, as conflicting
requests execution is serialized.
Request "T2 (update table2)" is blocked in the DB since rows have been locked by "T3
(update table2)".
So we have a deadlock.
The problem is that "T3 (insert into table1)" could be first executed taking
advantage of the database
row-level locking. When trying to execute it the second time around, the fact that
"T2 (update table2)"
was queued in the meantime brought up the table-level locking constraints.
Enforcing strict table-level locking as suggested by Olivier will avoid this problem, since
"T3 (insert into table1)" would not have been executed before T2, which holds the lock for table1,
committed.
There are no immediate plans to support row-level locking in Sequoia.
Damián
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
