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;