MySQL的lock tables和unlock tables使用详解

在mysql中锁表是一个比较有学问的东西了,下面我来给各位介绍一下MySQL的lock tables和unlock tables使用方法.

lock tables 命令是为当前线程锁定表.这里有2种类型的锁定,一种是读锁定,用命令 lock tables tablename read;另外一种是写锁定,用命令lock tables tablename write.下边分别介绍.

1.lock table 读锁定

如果一个线程获得在一个表上的read锁,那么该线程和所有其他线程只能从表中读数据,不能进行任何写操作,下边我们测试下,测试表为user表.

不同的线程,可以通过开多个命令行MySQL客户端来实现,时刻点,线程A（命令行窗口A）,线程B（命令行窗口B）,代码如下:

1. mysql>locktablesuserread;
2. QueryOK,0rowsaffected(0.00sec)
3. mysql>

对user表加读锁定,代码如下:

1. mysql>select*fromuser;
2. +------+-----------+
3. |id|name|
4. +------+-----------+
5. |22|abc|
6. |223|dabc|
7. |2232|dddabc|
8. |45|asdsagd|
9. |23|ddddddddd|
10. +------+-----------+
11. 5rowsinset(0.00sec)
12. mysql>

自己的读操作未被阻塞,代码如下:

1. mysql>select*fromuser;
2. +------+-----------+
3. |id|name|
4. +------+-----------+
5. |22|abc|
6. |223|dabc|
7. |2232|dddabc|
8. |45|asdsagd|
9. |23|ddddddddd|
10. +------+-----------+
11. 5rowsinset(0.00sec)
12. mysql>

其他线程的读也未被阻塞,代码如下:

1. mysql>insertintouservalues(12,'test');
2. ERROR1099(HY000):Table'user'waslockedwithaREADlockandcan'tbeupdated
3. mysql>

发现本线程的写操作被阻塞,代码如下:

mysql> insert into user values(22,'2test');

发现没有任何反应,一直等待中,说明没有得到写锁定,一直处于等待中.代码如下:

1. mysql>unlocktables;
2. QueryOK,0rowsaffected(0.00sec)
3. mysql>

释放读锁定.

1. mysql>insertintouservalues(22,'ddd');
2. QueryOK,1rowaffected(1min27.25sec)
3. mysql>

在线程A释放读锁后,线程B获得了资源,刚才等待的写操作执行了,代码如下:

1. mysql>locktablesuserreadlocal;
2. QueryOK,0rowsaffected(0.00sec)
3. mysql>

获得读锁定的时候增加local选项.

1. mysql>insertintouservalues(2,'b');
2. QueryOK,1rowaffected(0.00sec)
3. mysql>

发现其他线程的insert未被阻塞,代码如下:

mysql> update user set name = 'aaaaaaaaaaaaaaaaaaaaa' where id = 1;

但是其他线程的update操作被阻塞了.

注意:user表必须为Myisam表，以上测试才能全部OK，如果user表为innodb表，则lock tables user read local命令可能没有效果，也就是说，如果user表为innodb表，第6时刻将不会被阻塞，这是因为INNODB表是事务型的，对于事务表，例如InnoDB和BDB，--single-transaction是一个更好的选项，因为它不根本需要锁定表

2.lock table 写锁定

如果一个线程在一个表上得到一个WRITE锁,那么只有拥有这个锁的线程可以从表中读取和写表,其它的线程被阻塞.

写锁定的命令:lock tables user write.user表为Myisam类型的表。

参考如下测试:

时刻点

线程A（命令行窗口A）

线程B（命令行窗口B）

代码如下:

1. mysql>locktablesuserwrite;
2. QueryOK,0rowsaffected(0.00sec)

对user表加写锁定,代码如下:

1. mysql>select*fromuser;
2. +----+-----------------------+
3. |id|name|
4. +----+-----------------------+
5. |1|aaaaaaaaaaaaaaaaaaaaa|
6. |2|b|--phpfensi.com
7. +----+-----------------------+
8. 2rowsinset(0.00sec)

自己可以继续进行读操作,代码如下:mysql> select * from user;

其他线程读操作被阻塞,代码如下:

1. mysql>unlocktables;
2. QueryOK,0rowsaffected(0.00sec)

释放锁定,代码如下:

1. mysql>select*fromuser;
2. +----+-----------------------+
3. |id|name|
4. +----+-----------------------+
5. |1|aaaaaaaaaaaaaaaaaaaaa|
6. |2|b|
7. +----+-----------------------+
8. 2rowsinset(32.56sec)

其他线程获得资源,可以读数据了,以上所有结果均在MySQL 5.4.3下测试通过,在MySQL中如何用命令查看表是MyISAM类型还是INNODB类型的？代码如下:

show create table ***

ENGINE=MyISAM AUTO_INCREMENT=14696 DEFAULT CHARSET=utf8 |

mysql的锁表问题

SHOW PROCESSLIST查看数据库中表的状态,是否被锁；

1. killid//杀掉被锁的表
2. ===================================================
4. setautocommit=0;
5. select*fromt1whereuid='xxxx'forupdate//在有索引（例如uid）的情况下是行锁，否则是表锁
6. insertintot1values(1,'xxxxx');
7. commit;
9. locktablest1write|read;
10. insertintot1values(2,'xxxxx');//只有insert
11. unlocktables;