Home | 简体中文 | 繁体中文 | 杂文 | Github | 知乎专栏 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
知乎专栏

8.2. 事务处理和锁定语句

Transactional and Locking Statements

开始事务 begin、start transaction 或者 set autocommit=0

事务的特征:原子性(Atomiocity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability),这四个特性简称ACID特性。
    原子性:事务是数据库的逻辑工作单位,事务中包括的所有操作要么都做,要么都不做。
    一致性:事务执行的结果必须是使数据库从一个一致性的状态变到另外一个一致性状态。
    隔离性:一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对其他事务是隔离的,并发执行的各个事务之间互相不干扰。
    持久性:一个事务一旦成功提交,对数据库中数据的修改就是持久性的。接下来其他的其他操作或故障不应该对其执行结果有任何影响。
	

8.2.1. 事务隔离级别

事务隔离模式

1) READ UNCOMMITED
SELECT的时候允许脏读,即SELECT会读取其他事务修改而还没有提交的数据。

2)READ COMMITED
SELECT的时候无法重复读,即同一个事务中两次执行同样的查询语句,若在第一次与第二次查询之间时间段,其他事务又刚好修改了其查询的数据且提交了,则两次读到的数据不一致。

3) REPEATABLE READ
SELECT的时候可以重复读,即同一个事务中两次执行同样的查询语句,得到的数据始终都是一致的。实现的原理是,在一个事务对数据行执行读取或写入操作时锁定了这些数据行。
但是这种方式又引发了幻想读的问题。因为只能锁定读取或写入的行,不能阻止另一个事务插入数据,后期执行同样的查询会产生更多的结果。

4)SERIALIZABLE
与可重复读的唯一区别是,默认把普通的SELECT语句改成SELECT …. LOCK IN SHARE MODE。即为查询语句涉及到的数据加上共享琐,阻塞其他事务修改真实数据。
serializable模式中,事务被强制为依次执行。这是SQL标准建议的默认行为。
		

可以通过下列语句查询全局和当前会话的事务隔离级别:

SELECT @@global.tx_isolation;
SELECT @@tx_isolation;
		
查看InnoDB系统级别的事务隔离级别:
mysql> SELECT @@global.tx_isolation;

查看InnoDB会话级别的事务隔离级别:
mysql> SELECT @@tx_isolation;

修改InnoDB系统级别的事务隔离级别:
mysql> set global transaction isolation level read committed;

修改InnoDB会话级别的事务隔离级别:
mysql> set session transaction isolation level read committed;
		

8.2.2. 事务所用到的表

information_schema

		
select * from innodb_trx;
select * from innodb_lock_waits;
select * from innodb_locks;
		
		

8.2.3. 解决更新冲突

CREATE TABLE `account` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`user` VARCHAR(50) NOT NULL DEFAULT '0',
	`cash` FLOAT NOT NULL DEFAULT '0',
	`point` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`),
	UNIQUE INDEX `user` (`user`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
		
INSERT INTO `test`.`account` (`user`, `cash`,`point`) VALUES ('neo', 10,10);
		

下面通过account表,我来模拟一个返点场景,例如电商网站经常会用到“返点”,购买一定数量的商品赠送一定的点数,可以通过点数买东西,这样涉及到点的加于减操作。

表 8.1. 更新丢失演示

Session ASession B
select point into @point from account where user='neo';
					
select point into @point from account where user='neo';
					
update account set point=@point+20 where user='neo';
					
update account set point=@point+50 where user='neo';
					

看看最后用户有多少点?

		
mysql> select point from account where user='neo';
+-------+
| point |
+-------+
|    30 |
+-------+
1 row in set (0.00 sec)
		
		

傻了吧,老板发火,测试不能重现,运维说这是程序计算错误,程序员说程序没有错误,这样的场景国内很多公司都出现过吧?

问题出在哪里呢?出在并发上,很多web程序员很少考虑并发是产生的问题,怎么解决?很多方案,在我的职业生涯过程就见过很多奇葩方案,都能解决问题但不太完美。

如果更新语句改为 update account set point=@point+50 where user='neo' and point=@point; 会更保险,但仍然不能解决同意时间所产生的更新操作

下面是通过事务与锁彻底解决上面的问题。

		
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)		
		
		

检查事务隔离级别为:REPEATABLE-READ

表 8.2. 防止更新丢失加锁演示

Session ASession B
begin;
select point into @point from account where user='neo' for update;
					
begin;
select point into @point from account where user='neo' for update;
					

执行到此处会挂起

update account set point=@point+20 where user='neo';
commit;
					
update account set point=@point+50 where user='neo';
commit;
					

上面解决更新覆盖问题,但从数据库设计角度是不应该这样设计表的。仅供参考

CREATE TABLE `account` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`user` VARCHAR(50) NOT NULL DEFAULT '0',
	`cash` FLOAT NOT NULL DEFAULT '0',
	`point` INT(10) NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
		

每一次数据变化新增一条数据

INSERT INTO `test`.`account` (`user`, `point`) VALUES ('neo', -10);
INSERT INTO `test`.`account` (`user`, `point`) VALUES ('neo', -5);
INSERT INTO `test`.`account` (`user`, `point`) VALUES ('neo', 30);
INSERT INTO `test`.`account` (`user`, `point`) VALUES ('neo', -20);
		

计算剩余点数

select sum(point) as point from account where user='neo';
		

8.2.4. 共享锁

先加上共享锁,此时也会对mytable表加上IS锁

		
begin;
select * from mytable where id=1 for share; 		
		
		

观察IS锁

		
select * from performance_schema.data_locks;	
		
		
		
		
		
		

8.2.5. SAVEPOINT

DROP PROCEDURE IF EXISTS doOrder;

DELIMITER $$

CREATE PROCEDURE doOrder(IN orderUUID VARCHAR(40))
  BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_order;

    START TRANSACTION;
    SAVEPOINT sp_order;

    -- doing my updates and selects here...

    COMMIT;

  END $$

DELIMITER ;