mysql中lock tables与unlock tables(锁表/解锁)使用总结

转载https://www.cnblogs.com/ginikeer/p/4349704.html

mysql lock tables 使用有感

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" );
    }

关于lock自己可以再斟酌一下，因为第二个参数这样弄看上去并不太舒服。嗯哼~那怎测试呢？paperen使用jmeter进行测试结果

关于jmeter可以在http://jakarta.apache.org/site/downloads/downloads_jmeter.cgi 这里下载，在邪恶的人手中可以是一个恐怖的工具在善良的人手中是一个友好的工具。

您需要创建两个线程，其实就是对服务器发出两个请求。

 

具体配置paperen在此不说，我导出了一个计划文件，大家可以试着打开就能看到paperen是怎测试的了。http://iamlze.cn/demo/locktable/locktable.jmx

保存下来然后导入必需调整一下你本地测试的路径，最后ctrl+R（运行），在线程下查看结果树就有请求的回应信息了。

首先测试不加锁表的情况（就是一开始不加lock与unlock操作的代码）看看两个线程出来的结果。

 

都是ok~~再看数据库

 

然后将number改回1，再将lock与unlock，锁表操作加上，再运行。

 

好吧，数据表就不用看了吧，结果已经很明显了，再前一个请求对表操作完成之前，之后那些请求都要在等待，直到前面请求完成了才能操作，也就是队列的味道。

老实说mysql的事务也需要下点功夫研究一下，paperen关于锁表的了解也就是在查看事务的过程中产生的，在高级的应用过程中这种技术就更加重要，更加严谨的逻辑代码与严谨的数据库管理才能更进一步保证数据的真实与准确性。真是后知后觉。

posted @ 2020-06-03 18:04  edda_huang  阅读(1142)  评论(0)  编辑  收藏  举报