MySQL Forums
Forum List  »  InnoDB

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Index deadlock outside of transaction.
2668
March 11, 2009 05:15AM


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.