mysql 锁
表锁，锁住整个表
回话a mysql> lock tables sakila.film write; Query OK, 0 rows affected (0.00 sec) 回话b，执行任何语句就hang在那 mysql> select 1 from sakila.film LIMIT 1; 通过 show processlist;可以看到会话id 23正在看到表锁住table metadata lock mysql> show processlist; +----+------+-----------+--------+---------+------+---------------------------------+-----------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+--------+---------+------+---------------------------------+-----------------------------------+ | 26 | root | localhost | sakila | Query | 424 | Waiting for table metadata lock | select 1 from sakila.film LIMIT 1 | | 27 | root | localhost | sakila | Sleep | 428 | | NULL | | 28 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+------+-----------+--------+---------+------+---------------------------------+-----------------------------------+ 3 rows in set (0.00 sec)
行锁
回话a
update sakila.film set rental_duration=sleep(200) where film_id=1000;
回话b
update sakila.film set title='ZORRO ARK QDDS' where film_id=1000;
查看进程和锁
mysql> show processlist;
+----+------+----------------------+--------------------+---------+------+------------+----------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------------+--------------------+---------+------+------------+----------------------------------------------------------------------+
| 30 | root | localhost | sakila | Query | 11 | User sleep | update sakila.film set rental_duration=sleep(200) where film_id=1000 |
| 31 | root | 192.168.20.200:53718 | information_schema | Sleep | 932 | | NULL |
| 32 | root | 192.168.20.200:53731 | information_schema | Sleep | 1195 | | NULL |
| 33 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 34 | root | localhost | sakila | Query | 8 | updating | update sakila.film set title='ZORRO ARK QDDS' where film_id=1000 |
| 35 | root | localhost | NULL | Sleep | 2828 | | NULL |
| 36 | root | 192.168.20.200:53860 | ht | Sleep | 1271 | | NULL |
| 37 | root | 192.168.20.200:53861 | ht | Sleep | 1271 | | NULL |
+----+------+----------------------+--------------------+---------+------+------------+----------------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> SELECT
-> r.trx_id waiting_trx_id,
-> r.trx_mysql_thread_id waiting_thread,
-> r.trx_query waiting_query,
-> b.trx_id blocking_trx_id,
-> b.trx_mysql_thread_id blocking_thread,
-> b.trx_query blocking_query
-> FROM information_schema.innodb_lock_waits w
-> INNER JOIN information_schema.innodb_trx b
-> ON b.trx_id = w.blocking_trx_id
-> INNER JOIN information_schema.innodb_trx r
-> ON r.trx_id = w.requesting_trx_id \G
*************************** 1. row ***************************
waiting_trx_id: 2413493
waiting_thread: 34
waiting_query: update sakila.film set title='ZORRO ARK QDDS' where film_id=1000
blocking_trx_id: 2413492
blocking_thread: 30
blocking_query: update sakila.film set rental_duration=sleep(200) where film_id=1000
1 row in set, 1 warning (0.00 sec)
mysql> select * from sys.innodb_lock_waits\G
*************************** 1. row ***************************
wait_started: 2018-08-28 17:03:42
wait_age: 00:00:09
wait_age_secs: 9
locked_table: `sakila`.`film`
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 2413493
waiting_trx_started: 2018-08-28 17:03:42
waiting_trx_age: 00:00:09
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 34
waiting_query: update sakila.film set title='ZORRO ARK QDDS' where film_id=1000
waiting_lock_id: 2413493:176:19:26
waiting_lock_mode: X
blocking_trx_id: 2413492
blocking_pid: 30
blocking_query: update sakila.film set rental_ ... =sleep(200) where film_id=1000
blocking_lock_id: 2413492:176:19:26
blocking_lock_mode: X
blocking_trx_started: 2018-08-28 17:03:39
blocking_trx_age: 00:00:12
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 0
sql_kill_blocking_query: KILL QUERY 30
sql_kill_blocking_connection: KILL 30
1 row in set, 3 warnings (0.01 sec)
查看没有提交的事务也能看出来
SELECT
trx_id,
INNODB_TRX.trx_state,
INNODB_TRX.trx_started,
se.conn_id AS processlist_id,
trx_lock_memory_bytes,
se.USER,
se.command,
se.state,
se.current_statement,
se.last_statement
FROM
information_schema.INNODB_TRX,
sys.session AS se
WHERE
trx_mysql_thread_id = conn_id
mysql> SELECT
-> trx_id,
-> INNODB_TRX.trx_state,
-> INNODB_TRX.trx_started,
-> se.conn_id AS processlist_id,
-> trx_lock_memory_bytes,
-> se.USER,
-> se.command,
-> se.state,
-> se.current_statement,
-> se.last_statement
-> FROM
-> information_schema.INNODB_TRX,
-> sys.session AS se
-> WHERE
-> trx_mysql_thread_id = conn_id \G
*************************** 1. row ***************************
trx_id: 2413492
trx_state: RUNNING
trx_started: 2018-08-28 17:03:39
processlist_id: 30
trx_lock_memory_bytes: 1136
user: root@localhost
command: Query
state: User sleep
current_statement: update sakila.film set rental_ ... =sleep(200) where film_id=1000
last_statement: NULL
*************************** 2. row ***************************
trx_id: 2413493
trx_state: LOCK WAIT
trx_started: 2018-08-28 17:03:42
processlist_id: 34
trx_lock_memory_bytes: 1136
user: root@localhost
command: Query
state: updating
current_statement: update sakila.film set title='ZORRO ARK QDDS' where film_id=1000
last_statement: NULL
2 rows in set (0.05 sec)
innodb 行锁锁住时间默认时间为50秒，超过50秒就报错
mysql> show variables like 'innodb_lock_wait_timeout'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 50 | +--------------------------+-------+ 1 row in set (0.00 sec) mysql> update sakila.film set title='ZORRO ARK QDDS' where film_id=1000; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql8.0 锁 少一个视图information_schema.innodb_lock_waits，调整到sys schema下
当前被锁的语句
SELECT *
FROM performance_schema.events_statements_history
WHERE thread_id IN
(SELECT b. THREAD_ID
FROM sys. innodb_lock_waits AS a, performance_schema.threads AS b
WHERE a.waiting_pid = b. PROCESSLIST_ID)
ORDER BY timer_start ASC;
持锁的语句
SELECT *
FROM performance_schema.events_statements_history
WHERE thread_id IN
(SELECT b.THREAD_ID
FROM sys.innodb_lock_waits AS a, performance_schema.threads AS b
WHERE a.blocking_pid = b.PROCESSLIST_ID)
ORDER BY timer_start ASC;
