Hi Olivier, --- Olivier Fambon <[EMAIL PROTECTED]> wrote:
> One more question: do you have special constraints > on your tables, e.g. > foreign keys ? > No, I don't have any constrains for these 2 tables > So we have 2 transactions T2 and T3. > > T2 goes first, and grabs (should grab) the write > lock on table1. > This should block T3 when it tries to do "insert > into table1". > But apparently does not. > This is strange and un-expected (to me at least). > I think the first set of inserts in table1 are correct because the locks are not acquired at the table level, they are acquired at the row level on two different records as ROW EXCLUSIVE LOCK. So, they should not lock each other. This is how I see this scenario: T2 (insert into table1) grabs the row lock on table1 on a new record. - this is correct T3 (insert into table1) grabs the row lock on table1 on a new record (not the same as T2). - this is correct T3 (update table2) grabs the row lock on table2 for field1 = 1. - this is correct T2 (update table2) has to wait because it tries to update table2 for field1 = 1 (for the same record just updated by T3). At this moment T2 has to wait for T3 to commit. - this is correct This is where the problem starts: T3 (insert into table1) should grab the row lock on table1 on a new record (not as T2 or T3) and then commit. But what is happening is T3 it waits for something and then we get the deadlock because T2 waits for T3 and viceversa... I assume that the T3 - second insert in Table1 is locked by the "waiting" T2 - update on table2 (or anyway something similar) because if I remove the update statement from all this scenario everything works well, no deadlocks!!! Any thoughts? Thank you, Ioana Danes __________________________________________________ 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
