Details
-
Task
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
When a DB session running an UPDATE on locked row(s), killing the corresponding application (e.g. MySQL client) does not kill/remove that session in DB. The session is gone when the innodb_lock_wait_timeout is reached.
Scenario:
1. Create a simple table and insert a row:
CREATE TABLE `t1` (
`idx` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`idx`)
) ;
INSERT INTO t1 VALUES (1, 'abc'); COMMIT;
2. Run MySQL client to create session A to lock the row:
> SET AUTOCOMMIT=OFF;
> UPDATE t1 SET name='sess A' WHERE idx=1;
3. Run another MySQL client to create sesssion B, update the locked row by session A:
> SET AUTOCOMMIT=ON;
> UPDATE t1 SET name='sess B' WHERE idx=1;
4. Run SHOW FULL PROCESSLIST on another MySQL client, it shows session B is running running the UPDATE statement (waiting for the row to be unlocked).
5. On the OS shell, run "kill -9 <Session B MySQL client PID>" to kill the MySQL client of session B.
6. Run SHOW FULL PROCESSLIST on another MySQL client again, it shows session B still remains in the DB, running the UPDATE statement. Session B is gone until innodb_lock_wait_timeout is reached.
7. If session A commited the UPDATE before session B's innodb_lock_wait_timeout is reached, the row will be updated by session B.
I would suppose that killing application can kill the session in DB but it doesn't in the above scenario. Please advise the above behavior is normal or not.
Thanks.