mysql 表级锁

程序员子朋技术 2022年11月14日

0 收藏 752 点赞 3,572 浏览 1230 个字

表级锁：分为读锁和写锁: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 没有好的办法