mysql 表级锁

表级锁：分为读锁和写锁:

lock   tables   table_name   read;//其他事务只能读，不能加写锁，要等待更新。
 
SESSION 50 执行:
mysql> update test set name='ccc' where id=1;
		 
		 
mysql> show full processlist;
+----+---------+----------------------+--------+---------+------+------------------------------+---------------------------------------+
| Id | User    | Host                 | db     | Command | Time | State                        | Info                                  |
+----+---------+----------------------+--------+---------+------+------------------------------+---------------------------------------+
| 20 | monitor | 115.236.160.82:56186 | NULL   | Sleep   |  162 |                              | NULL                                  |
| 21 | monitor | 115.236.160.82:56187 | NULL   | Sleep   |  162 |                              | NULL                                  |
| 23 | monitor | 115.236.160.82:56208 | NULL   | Sleep   |   43 |                              | NULL                                  |
| 24 | monitor | 115.236.160.82:56209 | NULL   | Sleep   |   43 |                              | NULL                                  |
| 47 | monitor | 115.236.160.82:54858 | NULL   | Sleep   |   66 |                              | NULL                                  |
| 48 | monitor | 115.236.160.82:54859 | NULL   | Sleep   |   67 |                              | NULL                                  |
| 49 | root    | localhost            | DEVOPS | Query   |    0 | init                         | show full processlist                 |
| 50 | root    | localhost            | DEVOPS | Query   |   27 | Waiting for table level lock | update test set name='ccc' where id=1 |
+----+---------+----------------------+--------+---------+------+------------------------------+---------------------------------------+
8 rows in set (0.00 sec)

lock   tables   table_name write;//其他事务不能读

解锁:

unlock  tables;

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

行级锁：行锁是对索引加锁



目前对于表级锁查找持有的thread id 没有好的办法

转载于:https://www.cnblogs.com/zhaoyangjian724/p/6200073.html