mysql以表级锁,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 没有好的办法