从丢失更新问题讲讲msyql中的锁_综合

1.事务知识回顾

1.1事务中对同一行的update会阻塞

transaction 1	transaction 2
start transaction	start transaction
update user_product set click_count = 1 where id = 1;
	update user_product set click_count = 1 where id = 1;
	阻塞
commit
	更新成功

1.2事务的隔离性

result1和result2读出来的结果一样

transaction 1	transaction 2
start transaction	start transaction
update user_product set click_count = 1 where id = 1;
insert操作
	result1 = select * from user_product;
commit
	result2 = select * from user_product;

1.3非锁定读与锁定读

下表中如果transaction2不加for update，则可以直接读取成功

transaction 1	transaction 2
start transaction	start transaction
select * from user_product where id = 1 for update;
	select * from user_product where id = 1 for update;
	阻塞
commit

2.丢失更新问题实际场景

product表

id	sale_money	other_colunm
1	500	0

user_product表

id	user_id	product_id	click_max	click_count	other_column
1	1	1	500	499	0

user_product_click_record(id, user_product_id, user_id)

丢失更新场景（说明：对于上表，多个事务同时读取，并将click_count+1）

transaction 1	transaction 2
start transaction	start transaction
record = select * from user_product where user_id = 1 and product_id = 1;	record = select * from user_product where user_id = 1 and product_id = 1;
if(record.get('click_count') < record.get('click_max')) //添加发红包记录 else //已达上限	if(record.get('click_count') < record.get('click_max')) //添加发红包记录 else //已达上限
update user_product set click_count = record.get(”click_count“)+1 where id = record.get("id")
	update user_product set click_count = record.get(”click_count“)+1 where id = record.get("id")
commit
	commit

A和B同时进行了点击操作，导致实际点击数超过了click_max，也就是A的更新丢失了

3.如何解决->悲观锁&乐观锁

乐观锁在user_product表增加version字段

transaction 1	transaction 2
start transaction	start transaction
record = select * from user_product where user_id = 1 and product_id = 1;	record = select * from user_product where user_id = 1 and product_id = 1;
if(record.get('click_count') < record.get('click_max')) //发红包记录 else //已达上限	if(record.get('click_count') < record.get('click_max')) //发红包 else //已达上限
update user_product set click_count = record.get(”click_count“)+1, version = record.get('version')+1 where id = record.get("id") and version = record.get('version')
	update user_product set click_count = record.get(”click_count“)+1, version = record.get('version')+1 where id = record.get("id") and version = record.get('version')
commit	阻塞
	0 rows affected
	commit

悲观锁

transaction 1	transaction 2
start transaction	start transaction
record = select * from user_product where user_id = 1 and product_id = 1 for update;
if(record.get('click_count') < record.get('click_max')) //发红包 else //已达上限	record = select * from user_product where user_id = 1 and product_id = 1 for update;//读到的是transaction1已经提交的数据了
update user_product set click_count = record.get(”click_count“)+1 where id = record.get("id")	阻塞
commit（释放锁）	阻塞
	if(record.get('click_count') < record.get('click_max')) //发红包 else //已达上限
	update user_product set click_count = record.get(”click_count“)+1 where id = record.get("id")
	commit

4.事务和锁信息查看

select * from information_schema.innodb_trx\G; 事务信息

select * from information_schema.innodb_locks\G; 锁信息

select * from information_schema.innodb_lock_waits\G; 阻塞等待情况

5.锁的实现

next-key lock = 记录+范围，取决于是否唯一索引

record-lock（锁降级）:

transaction 1: select * from user_product where other_column_unique = 8 for update;

transaction 2: select * from user_product where other_column_unique = 8 for update;

next-key lock

transaction 1: select * from user_product where other_column_normal = 8 for update;//数据库中other_column_normal 值为1，2，5，8，13，16

transaction 2: insert into user_product (other_column_unique, other_column_normal ) values (55, 9)//插入不进去，因为（5，13）被锁住了

6.锁带来的问题

6.1死锁

AB-BA mysql wait-for-graph自动检测

transaction 1	transaction 2
start transaction	start transaction
insert	insert
select * from user_product where other_column_unique = 8 for update;
	select * from user_product where other_column_unique = 3 for update;
select * from user_product where other_column_unique = 3 for update;
阻塞
	select * from user_product where other_column_unique = 8 for update;
	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction（回滚）
获得锁	insert//不属于当前事务了

6.2死锁会自动回滚，超时不会回滚

show global variables like '%timeout%';

set innodb_lock_wait_timeout=50;

transaction 1	transaction 2
start transaction	start transaction
select * from user_product where other_column_unique = 8 for update;
insert	select * from user_product where other_column_unique = 3 for update;
select * from user_product where other_column_unique = 3 for update;
阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert//还在当前事务里
commit

7.其他

共享锁 lock in share mode，很少用到

8.附件：测试中使用的数据表

CREATE TABLE `user_product` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`user_id` int(10) unsigned NOT NULL DEFAULT '0',`product_id` int(10) unsigned NOT NULL DEFAULT '0',`click_count` int(10) unsigned NOT NULL DEFAULT '0',`click_max` int(10) unsigned NOT NULL DEFAULT '0',`other_column_unique` int(11) NOT NULL DEFAULT '0',`other_column_normal` int(11) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `unique_index` (`other_column_unique`),KEY `normal_index` (`other_column_normal`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1;INSERT INTO `user_product` VALUES
(1,1,1,  40,500,1, 1),
(2,1,2,  40,500,3, 3),
(3,1,9,  40,500,5, 5),
(4,1,4,  40,500,6, 6),
(5,1,13, 40,500,8, 8),
(6,1,10, 40,500,11,11),
(7,1,11, 40,500,13,13),
(10,1,12,40,500,19,19);