mysql a锁_MySQL 两种锁操作

最近开发中遇到一些问题，这里记录下Mysql中的两种表级别的锁。

读锁：所有的会话只能进行SELECT语句查询

lock tables table_name READ;

写锁：只有当前会话能增删改查，其他会话无法任何操作

lock tables table_name WRITE

下面是个简单测试：

首先测试Write

下面先开启一个客户端进行如下操作：

mysql> show open tables fromnumtest;+----------+-------+--------+-------------+

| Database | Table | In_use | Name_locked |

+----------+-------+--------+-------------+

| numtest | num | 1 | 0 |

+----------+-------+--------+-------------+

1 row in set (0.00sec)

mysql>lock tables num WRITE;

Query OK,0 rows affected (0.00sec)

mysql> show open tables fromnumtest;+----------+-------+--------+-------------+

| Database | Table | In_use | Name_locked |

+----------+-------+--------+-------------+

| numtest | num | 1 | 0 |

+----------+-------+--------+-------------+

1 row in set (0.00sec)

mysql> select count(*) fromnum;+----------+

| count(*) |

+----------+

| 1 |

+----------+

1 row in set (0.00 sec)

然后开启客户端二操作：

mysql> select count(*) from numtest.num;

此时 客户端二会被lock

查看processlist得到验证：

mysql>show processlist;+--------+------+-----------+---------+---------+------+---------------------------------+----------------------------------+

| Id | User | Host | db | Command | Time | State | Info |

+--------+------+-----------+---------+---------+------+---------------------------------+----------------------------------+

| 2 | root | localhost | numtest | Query | 0 | starting | show processlist |

| 101003 | root | localhost | NULL | Query | 7 | Waiting for table metadata lock | select count(*) from numtest.num |

+--------+------+-----------+---------+---------+------+---------------------------------+----------------------------------+

2 rows in set (0.00 sec)

然后客户端A 释放锁：

mysql>unlock tables;

Query OK,0 rows affected (0.00 sec)

客户端二 执行OK(被锁了18s)。

mysql> select count(*) fromnumtest.num;+----------+

| count(*) |

+----------+

| 1 |

+----------+

1 row in set (18.42 sec)

再测试Read Lock,同一个session操作。

mysql> lock tables num READ;

Query OK,0 rows affected (0.00sec)

mysql>mysql>mysql> insert into num values (1000,"2222");

ERROR1099 (HY000): Table 'num' was locked with a READ lock and can't be updated

mysql> show processlist;

+--------+------+-----------+---------+---------+------+----------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+--------+------+-----------+---------+---------+------+----------+------------------+

| 2 | root | localhost | numtest | Query | 0 | starting | show processlist |

| 101003 | root | localhost | NULL | Sleep | 2973 | | NULL |

+--------+------+-----------+---------+---------+------+----------+------------------+

2 rows in set (0.00 sec)

通过以上命令发现当前session也无法进行写操作

然后 释放锁，由client 2 进行写操作没问题

mysql>unlock tables;

Query OK,0 rows affected (0.00sec)

mysql> select count(*) fromnum;+----------+

| count(*) |

+----------+

| 2 |

+----------+

1 row in set (0.00sec)

mysql>mysql>show processlist;+--------+------+-----------+---------+---------+------+----------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+--------+------+-----------+---------+---------+------+----------+------------------+

| 2 | root | localhost | numtest | Query | 0 | starting | show processlist |

| 101003 | root | localhost | numtest | Sleep | 92 | | NULL |

+--------+------+-----------+---------+---------+------+----------+------------------+

死锁的一般情况可以通过超时时间设定、或者kill掉被锁住的进程。

以上！