놀멍

Kill, lock, mysql
OS환경 : Windows 10 Pro (64bit) + Pycharm


DB 환경 : Mysql 8.0.22

에러 : Lock wait timeout exceeded; try restarting transaction
파이참에서 mysql db에 dml 실행시 발생하는 메세지

sql_del = f"DELETE T1 FROM MYSQDB.TEST T1 WHERE T1.TEST= '{TEST}'"

pymysql.err.OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')




해결 방법 : lock 세션 kill 또는 트렌젝션 정상 종료
lock 세션 확인

mysql> SHOW PROCESSLIST;

Id|User           |Host           |db    |Command|Time  |State                 |Info                                                                          |
--+---------------+---------------+------+-------+------+----------------------+------------------------------------------------------------------------------+
5|event_scheduler|localhost      |      |Daemon |264640|Waiting on empty queue|                                                                              |
53|root           |localhost:13815|mysqdb|Sleep  |   973|                      |                                                                              |
54|root           |localhost:13859|mysqdb|Sleep  |   881|                      |                                                                              |
56|root           |localhost:14268|mysqdb|Sleep  |   745|                      |                                                                              |
57|root           |localhost:14298|      |Sleep  |   652|                      |                                                                              |
58|root           |localhost:14299|      |Sleep  |    94|                      |                                                                              |
59|root           |localhost:14300|mysqdb|Query  |     0|init                  |/* ApplicationName=DBeaver 23.3.0 - SQLEditor <Script.sql> */ SHOW PROCESSLIST|
60|root           |localhost:14309|      |Sleep  |    91|                      |                                                                              |
참고 다른쿼리 : select * from performance_schema.data_locks;





lock 유발 세션 kill

나의 경우 테스트 세션이기때문에 불필요한 세션을 모두 정리해줌

mysql>
kill 53
kill 54
kill 56
kill 57
kill 58
kill 60




lock 세션 재확인

mysql> SHOW PROCESSLIST;

Id|User           |Host           |db    |Command|Time  |State                 |Info                                                                          |
--+---------------+---------------+------+-------+------+----------------------+------------------------------------------------------------------------------+
5|event_scheduler|localhost      |      |Daemon |264954|Waiting on empty queue|                                                                              |
59|root           |localhost:14709|mysqdb|Query  |     0|init                  |/* ApplicationName=DBeaver 23.3.0 - SQLEditor <Script.sql> */ SHOW PROCESSLIST|
정상적으로 kill됨





dml 재실행

sql_del = f"DELETE T1 FROM MYSQDB.TEST T1 WHERE T1.TEST= '{TEST}'"

Name: 951, dtype: object
DELETE T1 FROM MYSQDB.TEST T1 WHERE T1.TEST = '99999'
INSERT INTO MYSQDB.TEST(TEST, TEST_NM, TESTCOL1, TESTCOL2)
dml이 정상적으로 수행됨





원인 : lock 세션으로 인한 dml 불가 현상
lock 세션으로 인한 dml 불가 현상





참조 : https://stackoverflow.com/questions/11523884/mysql-session-kill-query-to-unlock-table