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.
When a connected process is killed, the mariadb server doesn't receive an indication that the socket session has been removed.
I tested this an in a strace
1039777 <... poll resumed>) = 1 ([{fd=47, revents=POLLIN}])
1039777 recvfrom(47, "(\0\0\0", 4, MSG_DONTWAIT, NULL, NULL) = 4
1039777 recvfrom(47, "\3UPDATE t1 SET name='sess B' WHERE idx=1", 40, MSG_DONTWAIT, NULL, NULL) = 40
No error on file descriptor 47 or the poll of it return any error after the process was killed.
so normal behaviour it is.
With tcp keep alives you should be able to detect this earlier - https://mariadb.com/kb/en/server-system-variables/#tcp_keepalive_interval