Recently, I was working on one of the issue related to locks and deadlocks with InnoDB tables and I found very interesting details about how InnoDB locks and deadlocks works with or without index for different Isolation levels.
Here, I would like to describe a small test case about how SELECT ..FOR UPDATE (with and without limit) behave with INSERT/UPDATE and with READ-COMMITED and REPEATABLE-READ Isolation levels. I’m creating a small table data_col with few records. Initially, this test case was written by Bill Karwin to explain details to customer, but here I have used a bit modified test case.
CREATE TABLE data_col (dataname VARCHAR(10), period INT, expires DATE, host VARCHAR(10));
INSERT INTO data_col VALUES (‘med1′, …[Read more]