mysql中lock tables与unlock tables

2014-03-06 13:46 youxin 阅读(6502) 评论(0) 编辑收藏举报

官网：https://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE

UNLOCK TABLES

MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

Locks may be used to emulate transactions or to get more speed when updating tables. This is explained in more detail later in this section.

LOCK TABLES explicitly acquires table locks for the current client session. Table locks can be acquired for base tables or (as of MySQL 5.0.6) views. You must have the LOCK TABLES privilege, and the SELECT privilege for each object to be locked.

。。。

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

1. lock table 读锁定

如果一个线程获得在一个表上的read锁，那么该线程和所有其他线程只能从表中读数据，不能进行任何写操作。

下边我们测试下，测试表为user表。

不同的线程，可以通过开多个命令行MySQL客户端来实现：

时刻点
线程A（命令行窗口A）
线程B（命令行窗口B）
1
mysql> lock tables user read;

Query OK, 0 rows affected (0.00 sec)

mysql>

对user表加读锁定。

2
mysql> select * from user;

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

| id | name |

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

| 22 | abc |

| 223 | dabc |

| 2232 | dddabc |

| 45 | asdsagd |

| 23 | ddddddddd |

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

5 rows in set (0.00 sec)

mysql>

自己的读操作未被阻塞
mysql> select * from user;

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

| id | name |

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

| 22 | abc |

| 223 | dabc |

| 2232 | dddabc |

| 45 | asdsagd |

| 23 | ddddddddd |

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

5 rows in set (0.00 sec)

mysql>

其他线程的读也未被阻塞

3
mysql> insert into user values(12,'test');

ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated

mysql>

发现本线程的写操作被阻塞
其他线程:mysql> insert into user values(22,'2test');

发现没有任何反应，一直等待中，说明没有得到写锁定，一直处于等待中。

4
mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

mysql>

释放读锁定。
mysql> insert into user values(22,'ddd');

Query OK, 1 row affected (1 min 27.25 sec)

mysql>

在线程A释放读锁后，线程B获得了资源，刚才等待的写操作执行了。

5
mysql> lock tables user read local;

Query OK, 0 rows affected (0.00 sec)

mysql>

获得读锁定的时候增加local选项。
mysql> insert into user values(2,'b');

Query OK, 1 row affected (0.00 sec)

mysql>

发现其他线程的insert未被阻塞。(我测试还是阻塞，不知道为什么）

6

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> lock tables user write;

Query OK, 0 rows affected (0.00 sec)

对user表加写锁定。

2
mysql> select * from user;

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

| id | name |

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

| 1 | aaaaaaaaaaaaaaaaaaaaa |

| 2 | b |

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

2 rows in set (0.00 sec)

自己可以继续进行读操作
mysql> select * from user;

其他线程读操作被阻塞。

3
mysql> unlock tables ;

Query OK, 0 rows affected (0.00 sec)

释放锁定。

4
mysql> select * from user;

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

| id | name |

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

| 1 | aaaaaaaaaaaaaaaaaaaaa |

| 2 | b |

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

2 rows in set (32.56 sec)

其他线程获得资源，可以读数据了。

以上所有结果均在MySQL 5.4.3下测试通过。

在MySQL中如何用命令查看表是MyISAM类型还是INNODB类型的？

show create table ***

ENGINE=MyISAM AUTO_INCREMENT=14696 DEFAULT CHARSET=utf8 |

-----------------------------

一篇文章：

mysql 的表锁 lock tables 感觉就像一个封闭的空间

mysql发现 lock tables 命令的时候,会将带有锁标记的表(table) 带入封闭空间,直到出现 unlock tables 命令或线程结束, 才关闭封闭空间。

进入封闭空间时 , 仅仅只有锁标记的表(table) 可以在里面使用,其他表无法使用。

锁标记分为 read 和 write 下面是两种锁的区别

--------------------------------------------------------------------

//如将 table1 设为read锁, table2 设为write锁, table3 设为read锁

lock tables [table1] read,[table2] write,[table3] read;
----------------------------------------------------------------------

//执行到这里时,进入封闭空间。

1. table1 仅允许[所有人]读,[空间外]如需写、更新要等待[空间退出],[空间内]如需写、更新会引发mysql报错。
2. table2 仅允许[空间内]读写更新,[空间外]如需写、更新要等待[空间退出]。
3. table3 仅允许[所有人]读,[空间外]如需写、更新要等待[空间退出],[空间内]如需写、更新会引发mysql报错。

----------------------------------------------------------------------
//执行到这里时,退出封闭空间,释放所有表锁

unlock tables
----------------------------------------------------------------------

当前线程关闭时,自动退出封闭空间,释放所有表锁,无论有没有执行 unlock tables

上面一堆东西感觉很乱，下面我们看个实例吧。

在某个地方看到有个例子，具体描述类似如下：商店现在某商品只有1件库存，然后A与B在网上进行下订，A与B几乎同时（或许也就差几毫秒，A比B快那么一点点）进行。

很明显是只有A才能成功下单的，B则会收到库存不足的提示，但是作为放置在服务端的那个页面（或者称为脚本程序）我们得怎样去处理这个问题呢？或者我先放出一段代码吧。

 $sql = "select number from goods where id=1";
    $number = intval( $db->result( $db->query( $sql ), 0 ) );
    if ( $number > 0 ) {
    sleep( 2 );
    $sql = "update goods set number=number-1 where id = 1";
    if ( $db->query( $sql ) ) {
    echo 'Ok!Here you are!';
    } else {
    echo 'Sorry!Something go wrong!Try it again.';
    }
    } else {
    echo 'No more!you are so late!';
    }

这部分代码除了缺少一定注释外都写得没错，当然$db是一个操作数据库的类，我只是将大部分方法封装了，这里的逻辑也是很明显了。

先获取id为1这个东东的库存数，看看是否为0，如果为0就订购不成功了，如果大于0则将库存减1然后提示ok。这确实没有任何错误，逻辑也对。如果请求是一个接一个地产生的，那么什么问题都没有，但当一些并发情况（paperen也不想用这种专业的名词，其实就是上面那个例子的情况，在相差不明显的时间内有多个请求产生）出现时就可能出现一些无厘头的问题了。你想啊，是不是可能存在一种情况，A刚发出请求，脚本处理到update之前B又发出请求，那么现在库存依然还有1，因为A的update还没有执行呢，所以$number不少于0，这次完了，B也下单了，于是库存变成-1了（假设原来只有1件），确实是一个荒谬而且比较搞笑的结果。

出现问题的原因很明显，就是忽略了这种并发情况的考虑，处理下订应该是种队列方式，也就是先来先得，就是说在执行这个下订动作是要排队的，前面的那个先下订然后后者才能下订，当然当后者下订前才再判断库存的数量。那么怎样解决这个问题呢，在程序层面上貌似真的没有方法去解决这个问题（paperen可没想到代码上的解决方案，有思路的可以留个言），所以在此才提到锁表的概念，你想啊，上面出现这个问题的归根于没有控制一个select number的先后顺序（或者可以这么说吧），因为在A执行update之前你又允许B去查询库存，当然结果还是1，至少要等待A更新库存后才允许其他人的任何操作，也就是对goods表进行一个排队操作，对goods表进行锁定。

说到这里，请不要以为锁表有多么高深，其实它就是一条sql

LOCK TABLE `table` [READ|WRITE]

解锁

UNLOCK TABLES;

引用专业的描述是

LOCK TABLES为当前线程锁定表。 UNLOCK TABLES释放被当前线程持有的任何锁。当线程发出另外一个LOCK TABLES时，或当服务器的连接被关闭时，当前线程锁定的所有表会自动被解锁。

如果一个线程获得在一个表上的一个READ锁，该线程和所有其他线程只能从表中读。如果一个线程获得一个表上的一个WRITE锁，那么只有持锁的线程READ或WRITE表，其他线程被阻止。

已经是有种队列的味道，对不，所以解决方案很简单嘛，在select前加锁，执行完后面逻辑代码后解锁。或许有没有人会有一个疑问，就是如果万一锁表后线程就断掉了那么是不是就一直锁表了，这个确实是可能存在但是既然你想到了那么数据库的设计人员也一定考虑到了，可以告诉你关于unlock的一些资料：当线程发出另一个 LOCK TABLES，或当与服务器的连接被关闭时，被当前线程锁定的所有表将被自动地解锁。这下放心了吧。

好，看下改进后的代码。

$db->lock( 'goods', 2 );
    $sql = "select number from goods where id=1";
    $number = intval( $db->result( $db->query( $sql ), 0 ) );
    if ( $number > 0 ) {
    sleep( 2 );
    $sql = "update goods set number=number-1 where id = 1";
    if ( $db->query( $sql ) ) {
    echo 'Ok!Here you are!';
    } else {
    echo 'Sorry!Something go wrong!Try it again.';
    }
    } else {
    echo 'No more!you are so late!';
    }
    $db->unlock();

只加了两行代码，不过也不能这么说，因为paperen我修改了自己那个操作数据库的类，加了两个方法lock与unlock，其实这两个方法也很简单。

  /**
    * 锁表
    * @param string $table 表名
    * @param int $type 读锁1还是写锁2
    */
    public function lock( $table, $type = 1 ) {
    $type = ( $type == 1 ) ? 'READ' : 'WRITE';
    $this->query( "LOCK TABLE `$table` $type" );
    }
     
    /**
    * 解锁
    */
    public function unlock() {
    $this->query( "UNLOCK TABLES" );
    }