mysql 并发下数据不一致的问题分析及解决

MySQL 5.6 , InnoDB存储引擎，默认事务隔离级别(REPEATABLE-READ)

初始sql 脚本如下:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Test`(out debitb decimal(14,2))
BEGIN

START TRANSACTION ;

select @db:=debit_balance from c_account_customer where id=1 ;
set debitb=@db;
insert into abacus.testvalue (val) values (@db);
update abacus.c_account_customer set debit_balance=@db+1 where id=1;
commit;

END

如上，存储过程中开启事务，先查询debit_balance，查询结果插入testvalue数据表，然后更新debit_balance(加1)

100个并发操作

客户端，同时开启一百个线程，每个线程都调用存储过程Test。

假设数据表c_account_customer中的字段debit_balance初始值为10000.

那么客户端程序执行完成后，理想情况下debit_balance=100、testvalue 数据表有100数据，val值为0-100。

看看结果：

如上，数据未达到预期的原因是在某一时刻，事务A读取debit_balance值时并未锁住数据，事务B(或许更多事务)此时也读到了相同的值，

那么这些事务总体只对debit_balance进行了加1操作。那么如何解决以上问题？即当一个事务读取数据时，锁住数据行，在提交之前，其他事务不能执行。

 

mysql ：select ... for update 

修改sql脚本：

CREATE DEFINER=`root`@`localhost` PROCEDURE `Test`(out debitb decimal(14,2))
BEGIN

START TRANSACTION ;

select @db:=debit_balance from c_account_customer where id=1 for update;
set debitb=@db;
insert into abacus.testvalue (val) values (@db);
update abacus.c_account_customer set debit_balance=@db+1 where id=1;
commit;

END

如上，在查询语句后面加上 for update

首先我们来看看并发操作后的结果：

 

通过图例，我们发现在查询语句中加入for update 解决了上面存在的问题。即我们在查询出debit_balance后就把当前的数据锁定，直到我们修改完毕后再解锁.

 

现在我们来看看 for update：

在事务中，SELECT ... FOR UPDATE 同一笔数据时会等待其它事务结束后才执行，一般SELECT ... 则不受此影响拿上面的实例来说，当我执行select debit_balance from c_account_customer where id=1 for update;后。我在另外的事务中如果再次执行select debit_balance from c_account_customer where id=1 for update;则第二个事务会一直等待第一个事务的提交，此时第二个查询处于阻塞的状态，但是如果我是在第二个事务中执行select debit_balance from c_account_customer where id=1;则能正常查询出数据，不会受第一个事务的影响 (经过测试)。

补充：MySQL select…for update的Row Lock与Table Lock
上面我们提到，使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。