Hi Damian and Olivier, Unfortunately enforcing strict table-level locking is not a solution for us. In this scenario and the TPS dropped from 122 to 18, which is unacceptable...
Thank you, Ioana --- Damian Arregui <[EMAIL PROTECTED]> wrote: > 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 > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com _______________________________________________ Sequoia mailing list [email protected] https://forge.continuent.org/mailman/listinfo/sequoia
