mysql中使用事务和行锁解决并发修改的问题

来源：互联网发布：南昌淘宝摄影工作室编辑：程序博客网时间：2024/06/01 21:37

事务在一些比较重要的业务中会采用，比如：针对用户账户表的金额更改操作时就要尽可能避免多个会话同时修改金额，因为那样会导致读取的数据不一致，所以要考虑事务和行锁机制。

首先，mysql的数据引擎需要是InnoDB，InnoDB支持事务，其次加锁必须跟事务同时使用，还有查询的时候必须带锁，也就是说查询语句的后面要加入for update

用户A执行事务，为了防止在操作过程中其他用户也来操作这个表，加入for update

 mysql> start transaction;                         #####步骤1Query OK, 0 rows affected (0.00 sec)mysql> select * from users where id=3 for update;  #####步骤2+----+-------------+-----+-------------------+---------------------+| id | username    | age | email             | createtime          |+----+-------------+-----+-------------------+---------------------+|  3 | wangxiaowen |  34 | wangxiaowen@1.com | 2017-03-10 16:56:37 |+----+-------------+-----+-------------------+---------------------+1 row in set (0.00 sec)mysql> update users set age=35 where id=3;         #####步骤3Query OK, 1 row affected (0.03 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> commit;                                       #####步骤4Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> select age,email from users where id=3 for update;+-----+-------------------+| age | email             |+-----+-------------------+|  35 | wangxiaowen@a.com |+-----+-------------------+1 row in set (0.00 sec)mysql> update users set age=36 where id=3;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> commit;Query OK, 0 rows affected (0.00 sec)

用户B也要更新该条数据，为要更新也加入更新的读锁，会发现，当用户A在进行到步骤2之后，步骤B的查询语句没有结果，直到用户A提交了事务（或回滚了事务）之后，才出现查询结果，而且是已经更新后的查询结果。

mysql> select * from users;                         #####步骤A+----+-------------+-----+-------------------+---------------------+| id | username    | age | email             | createtime          |+----+-------------+-----+-------------------+---------------------+|  3 | wangxiaowen |  34 | wangxiaowen@1.com | 2017-03-10 16:56:37 ||  4 | hezhaozhao  |  33 | hezhao@163.com    | 2017-01-05 00:00:00 ||  5 | guoxiaofeng |  34 | guoxiaofeng@1.com | 2017-01-06 00:00:00 ||  6 | wanggengke  |  31 | wanggengke@12.com | 2017-05-04 00:00:00 ||  7 | huqilong    |  39 | huqilong@1.com    | 2017-02-26 00:00:00 |+----+-------------+-----+-------------------+---------------------+5 rows in set (0.01 sec)mysql> select * from users where id=3 for update;  #####步骤B+----+-------------+-----+-------------------+---------------------+| id | username    | age | email             | createtime          |+----+-------------+-----+-------------------+---------------------+|  3 | wangxiaowen |  35 | wangxiaowen@1.com | 2017-03-10 16:56:37 |+----+-------------+-----+-------------------+---------------------+1 row in set (25.48 sec)

mysql> select age email from users where id=3;+-------+| email |+-------+|    35 |+-------+1 row in set (0.01 sec)mysql> select age,email from users where id=3;+-----+-------------------+| age | email             |+-----+-------------------+|  35 | wangxiaowen@a.com |+-----+-------------------+1 row in set (0.00 sec)mysql> select age,email from users where id=3 for update;^C^C -- query abortedERROR 1317 (70100): Query execution was interruptedmysql> select username from users where id=3 for update;+-------------+| username    |+-------------+| wangxiaowen |+-------------+1 row in set (42.86 sec)

for update其实针对的是整行，即使在一个用户事务中我们只查询了email和age两个字段，另外一个用户想修改username,也是不允许的。

thinkphp中主要是针对model对象通过lock(true)方法来达到加锁的目的。
比如: $user_mod->lock(true)->where('id=1')->select();

这个地方用锁查询，如果查询这个user对象需要防止同时操作的话也要进行加锁，就是也要用lock(true)这种方式查询：
代码示例如下：

   M()->startTrans();//开启事务            $map['userid']='test';//查询条件            $user = M('User')->lock(true)->where($map)->find();//加锁查询            if($user)            {                //执行你想进行的操作, 最后返回操作结果 result                $result = true;                if(!$result)                {                    M()->rollback();//回滚                    $this->error('错误提示');                }            }            M()->commit();//事务提交            $this->success('成功提示');

加上lock(true)的实际就是在查询语句最后加上 for update

0 0