I am a bit confused about Innodb deadlock and the "SELECT…FOR UPDATE” statement.
1. Here is a deadlock example
1) Create actor table
Expand|Select|Wrap|Line Numbers
- CREATE TABLE actor(actor_id INT NOT NULL,
- first_name VARCHAR(10),
- last_name VARCHAR(10),
- PRIMARY KEY(actor_id)
- )ENGINE=INNODB;
Expand|Select|Wrap|Line Numbers
- INSERT INTO actor VALUES(1,’Penelope’,’Guiness’);
Expand|Select|Wrap|Line Numbers
- session 1 session 2
- -------------------------------------------------------------------------
- SET autocommit=0; | SET autocommit=0;
- -------------------------------------------------------------------------
- INSERT INTO actor VALUES |
- (1,'Peter','Pan'); |
- ERROR 1582(23000):Duplicate |
- entry '1' for key ‘PRIMARY’ |
- -------------------------------------------------------------------------
- | SELECT * FROM actor
- | WHERE actor_id=1 FOR UPDATE;
- | (Waiting for lock...)
- ------------------------------------------------------------------------
- UPDATE actor SET last_name |
- ='White' WHERE actor_id=1; |
- ERROR 1213(40001):Deadlock |
- Found when trying to get lock;|
- try restarting transaction |
- -------------------------------------------------------------------------
2. Does the “SELECT…FOR UPDATE” statement get different locks in different transaction isolation levels? I use the actor table(created previously), open two sessions like this,
Expand|Select|Wrap|Line Numbers
- session 1 session2
- -----------------------------------------------------------------------------
- SET session transaction isolation level | SET session transaction isolation level
- READ COMMITTED; | READ COMMITTED;
- -----------------------------------------------------------------------------
- SET autocommit=0; | SET autocommit=0;
- -------------------------------------------------------------------------------
- SELECT * FROM actor WHERE | SELECT * FROM actor WHERE
- actor_id=100 for update; | actor_id=100 for update;
- Empty set(0.00sec) | Empty set(0.00sec)
- -----------------------------------------------------------------------------
- INSERT INTO actor VALUES |
- (100,'Tom','Smith'); |
- Query ok, 1 row affected(0.00sec) |
- -----------------------------------------------------------------------------
But if I change the transaction isolation level from READ COMMITTED to REPEATABLE READ, the INSERT statement in session 1 will wait session 2 to release the lock. It appears that the “SELECT…FOR UPDATE” statement in session 2 holds a share lock on this record.
Does the “SELECT…FOR UPDATE” statement behave different in different isolation levels?
3. In which circumstance should we use the “SELECT…FOR UPDATE” or “SELECT…LOCK IN SHARE MODE” statement? Why we can not directly use the “INSERT” or “UPDATE” statement instead?
4. The deadlock seems inevitable; can I use the following statements to deal with it?
Expand|Select|Wrap|Line Numbers
- DECLARE CONTINUE|EXIT HANDLER FOR SQLSTATE ‘40001’
- BEGIN
- handling deadlock statements
- END