Re: Index deadlock outside of transaction.
Posted by:
Aftab Khan
Date: March 11, 2009 05:15AM
An UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned. If the locks to be set are exclusive, InnoDB also retrieves the clustered index ( in your case it's serverRequestQueueID) record and sets a lock on it. That's why innodb Rollback Tx1 ( which is INSERT STATMENT).
>btw I should have mentioned the global isolation level is set to >REPEATABLE-READ.
By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable disabled
Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint and duplicate-key checking.
Hope answered the question!
Subject
Views
Written By
Posted
4062
March 07, 2009 09:00PM
Re: Index deadlock outside of transaction.
2668
March 11, 2009 05:15AM
2421
March 15, 2009 06:37AM
2185
March 15, 2009 10:51PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.