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 (将整个数据表单给锁住)。

 

posted @ 2015-08-19 14:31  zzhi.wang  阅读(3591)  评论(0编辑  收藏  举报