mysql之事务锁（三）

目录
事务
事务特性ACID
事务带来的问题
事务隔离级别
锁
表锁行锁
Innodb锁类型
共享锁 Shared Locks
排它锁 Exclusive Locks
意向锁 (IS 、IX)
自增锁 AUTO-INC Locks
行锁算法
临键锁 Next-key Locks
间隙锁 Gap Locks
记录锁 Record Locks
死锁
利用锁解决事务问题
解决脏读
解决不可重复读
解决幻读

事务

事务:数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；事务是一组不可再分割的操作集合（工作逻辑单元）; 典型事务场景(转账)：

代码语言：javascript复制

update user_account set balance = balance - 1000 where userID = 3;
update user_account set balance = balance  1000 where userID = 1;

mysql中如何开启事务：

代码语言：javascript复制

begin 或者 start transaction -- 手工
commit / rollback -- 事务提交或回滚
set session autocommit = on/off; -- 设定事务是否自动开启

示例：

代码语言：javascript复制

--查看事务自动提交状态
show VARIABLES like 'autocommit';

-- autocommit 为 ON  
update teacher set name ='seven' where id =1;
insert teacher (name,age) value ('james',22);
delete from teacher where name = 'james';


-- autocommit 为 ON  如何开启事务
BEGIN;   
START TRANSACTION;

update teacher set name ='seven' where id =1;
insert teacher (name,age) value ('james',22);
delete from teacher where name = 'james';

COMMIT;
ROLLBACK;


-- 将autocommit 改成OFF
set session autocommit = OFF;
update teacher set name ='seven' where id =1;
insert teacher (name,age) value ('james',22);
delete from teacher where name = 'james';

commit;
ROLLBACK;

JDBC 编程：

代码语言：javascript复制

connection.setAutoCommit（boolean）;

Spring 事务AOP编程： expression=execution（com.gpedu.dao.*.*(..)）

事务特性ACID

原子性（Atomicity）最小的工作单元，整个工作单元要么一起提交成功，要么全部失败回滚一致性（Consistency）事务中操作的数据及状态改变是一致的，即写入资料的结果必须完全符合预设的规则，不会因为出现系统意外等原因导致状态的不一致隔离性（Isolation）一个事务所操作的数据在提交之前，对其他事务的可见性设定（一般设定为不可见）持久性（Durability）事务所做的修改就会永久保存，不会因为系统意外导致数据的丢失

事务带来的问题

脏读：事务未提交对其他事务也是可见的

不可重复读：一个事务开始之后，只能看到自己提交的事务所做的修改，也叫不可重复读（nonrepeatable read）

幻读：当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录当之前的事务再次读取该范围的记录时，会产生幻行。

事务隔离级别

SQL92 ANSI/ISO标准： http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Read Uncommitted（未提交读）：事务的修改，即使没有提交对其他事务也都是可见的 --事务可以读取未提交的数据，被称为脏读
Read Committed（提交读） --解决脏读问题一个事务开始之后，只能看到自己提交的事务所做的修改，换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。
Repeatable Read (可重复读) --解决不可重复读问题可重复读是在开始读取数据（事务开始）时不再允许修改操作。在同一个事务中多次读取同样的数据结果是一样的，这种隔离级别未定义解决幻读的问题。所谓幻读当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录当之前的事务再次读取该范围的记录时，会产生幻行。Innodb和XtraDB存储引擎通过多版本并发控制解决幻读问题。 **可重复读是mysql的默认事务隔离级别。**可就是说
Serializable（串行化） --解决所有问题最高的隔离级别，通过强制事务的串行执行，避免了前面说的幻读问题。

Innodb对隔离级别的并发程度：

Innodb是怎么解决幻读问题的呢？

加锁的读取是通过临键锁锁住要读取的行不让修改不加锁的读取是通过MVCC 版本号控制读取比

隔离级别的实现：锁 MVCC

锁

表锁行锁

锁是用于管理不同事务对共享资源的并发访问表锁与行锁的区别：锁定粒度：表锁 > 行锁加锁效率：表锁 > 行锁冲突概率：表锁 > 行锁并发性能：表锁 < 行锁 InnoDB存储引擎支持行锁和表锁（另类的行锁）

Innodb锁类型

用到的表：

代码语言：javascript复制

-- ----------------------------
-- Table structure for `users`
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `age` int(11) NOT NULL,
  `phoneNum` varchar(32) NOT NULL,
  `lastUpdate` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_eq_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES ('1', 'seven', '26', '13666666666', '2018-12-07 19:22:51');
INSERT INTO `users` VALUES ('2', 'qingshan', '19', '13777777777', '2018-12-08 21:01:12');
INSERT INTO `users` VALUES ('3', 'james', '20', '13888888888', '2018-12-08 20:59:39');
INSERT INTO `users` VALUES ('4', 'tom', '99', '13444444444', '2018-12-06 20:34:10');
INSERT INTO `users` VALUES ('6', 'jack', '91', '13444444544', '2018-12-06 20:35:07');
INSERT INTO `users` VALUES ('11', 'jack1', '33', '13441444544', '2018-12-06 20:36:19');
INSERT INTO `users` VALUES ('15', 'tom2', '30', '1344444444', '2018-12-08 15:08:24');
INSERT INTO `users` VALUES ('19', 'iiii', '30', '1344444444', '2018-12-08 21:21:47');

共享锁 Shared Locks

共享锁: 又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改; 加锁释锁方式：

代码语言：javascript复制

select * from users WHERE id=1 LOCK IN SHARE MODE;

释放方式：

代码语言：javascript复制

commit/rollback

示例：

代码语言：javascript复制

-- 共享锁加锁
BEGIN
select * from users WHERE id=1 LOCK IN SHARE MODE;


rollback; 
commit;

代码语言：javascript复制

-- 其他事务执行
--成功
select * from users where id =1;
--失败
update users set age=19 where id =1;

排它锁 Exclusive Locks

排他锁: 又称为写锁，简称X锁，排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁（共享锁、排他锁），只有该获取了排他锁的事务是可以对数据行进行读取和修改，（其他事务要读取数据可来自于快照）加锁方式：

代码语言：javascript复制

delete / update / insert 默认加上X锁
SELECT * FROM table_name WHERE ... FOR UPDATE

释锁方式：

代码语言：javascript复制

commit/rollback

示例：

代码语言：javascript复制

set session autocommit = OFF;
update users set age = 23 where id =1;
select * from users where id =1;
update users set age = 26 where id =1;

commit;
ROLLBACK;

-- 手动获取排它锁
set session autocommit = ON;
begin
select * from users where id =1 for update;
commit;

代码语言：javascript复制

-- 其他事务执行
--等待
select * from users where id =1 lock in share mode;
--等待
select * from users where id =1 for update;
--成功
select * from users where id =1;

InnoDB的行锁是通过给索引上的索引项加锁来实现的。 只有通过索引条件进行数据检索，InnoDB才使用行级锁，否则，InnoDB将使用表锁（锁住索引的所有记录） 表锁：lock tables xx read/write；

示例：

users表的索引项是name

代码语言：javascript复制

----ex1
set session autocommit = OFF;
update users set lastUpdate=NOW() where phoneNum = '13666666666';

rollback;
-- 其他查询执行
--等待
update users set lastUpdate=NOW() where id =2;
--等待
update users set lastUpdate=NOW() where id =1;

排他锁没走索引所有锁住了整个表导致其他事物的所有更新操作不能执行

代码语言：javascript复制

----- ex2
set session autocommit = OFF;
update users set lastUpdate=NOW() where id = 1;

rollback;
-- 其他查询执行
--成功
update users set lastUpdate=NOW() where id =2;
--等待
update users set lastUpdate=NOW() where id =1;

排它锁走了主键索引导致主键id为1的数据排它锁走了主键索引导致主键id为1的数据锁住行锁级别其他数据事务操作成功执行

代码语言：javascript复制

--------ex3
set session autocommit = OFF;
update users set lastUpdate=NOW() where `name` = 'seven';

rollback;
-- 其他查询执行
--等待
update users set lastUpdate=NOW() where `name` = 'seven';
--等待
update users set lastUpdate=NOW() where id =1;
--成功
update users set lastUpdate=NOW() where `name` = 'qingshan';
--成功
update users set lastUpdate=NOW() where id =2;

排它锁走了索引name 锁住name为seven的数据同时也在对应的主键索引加锁加两把锁

意向锁 (IS 、IX)

意向共享锁(Intention Shared Locks 简称IS)：表示事务准备给数据行加入共享锁，即一个数据行加共享锁前必须先取得该表的IS锁，意向共享锁之间是可以相互兼容的意向排它锁(Intention Exclusive Locks 简称IX)：表示事务准备给数据行加入排他锁，即一个数据行加排他锁前必须先取得该表的IX锁，意向排它锁之间是可以相互兼容的意向锁(IS 、IX) 是InnoDB 数据操作之前自动加的，不需要用户干预意义：

意向锁相当于一个全局flag 只要有一行记录有锁 flag就设置为true 当事务想去进行锁表时，可以先判断意向锁是否存在，存在时则可快速返回该表不能 启用表锁

代码语言：javascript复制

----- IS锁的意义
set session autocommit = OFF;
update users set lastUpdate=NOW() where id = 1;
rollback;

-- 其他会话执行
--等待   
update users set lastUpdate=NOW() where phoneNum = '13777777777';

等待因为这个操作不走索引锁全表但上边的操作已经对id为1的行锁住了所以他无法执行处于等待状态

自增锁 AUTO-INC Locks

针对自增列自增长的一个特殊的表锁 show variables like 'innodb_autoinc_lock_mode'; 默认取值1 ，代表连续，事务未提交ID永久丢失

代码语言：javascript复制

begin;
insert into users(name , age ,phoneNum ,lastUpdate ) values ('tom2',30,'1344444444',now());
ROLLBACK;

begin;
insert into users(name , age ,phoneNum ,lastUpdate ) values ('xxx',30,'13444444444',now());
ROLLBACK;


-- 其他事务执行
--等待
insert into users(name , age ,phoneNum ,lastUpdate ) values ('yyy',30,'13444444444',now());

行锁算法

用到的表：

代码语言：javascript复制

DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t2
-- ----------------------------
INSERT INTO `t2` VALUES ('1', '1');
INSERT INTO `t2` VALUES ('4', '4');
INSERT INTO `t2` VALUES ('7', '7');
INSERT INTO `t2` VALUES ('10', '10');

临键锁 Next-key Locks

区间左开右闭

命中区域所在的区间以及下一个区间都会被锁住即命中7 锁住7锁住的区间和下一个区间

代码语言：javascript复制

begin;
select * from t2 where id>5 and id<9 for update;

ROLLBACK

-- 其他事务执行
set session autocommit=off;
--执行成功
select * from t2 where id=4 for update;
--等待
select * from t2 where id=7 for update;
--等待
select * from t2 where id=10 for update;
--等待
INSERT INTO `t2` (`id`, `name`) VALUES (9, '9');

为什么Innodb选择临键锁next-key作为行锁的默认算法？

防止幻读

间隙锁 Gap Locks

开区间

gap只在RR事务级别存在

代码语言：javascript复制

begin;
select * from t2 where id >4 and id <6 for update;
-- 或者
select * from t2 where id =6 for update;

ROLLBACK;

-- 其他会话执行
--等待
INSERT INTO `t2` (`id`, `name`) VALUES (5, '5');
--等待
INSERT INTO `t2` (`id`, `name`) VALUES (6, '6');

两条插入数据执行不了因为区间（4，7）被锁住

记录锁 Record Locks

代码语言：javascript复制

begin;
select * from t2 where id =4 for update;
rollback;


-- 其他事务执行 
--成功
select * from t2 where id =7 for update;
--等待
select * from t2 where id =4 for update;

死锁

多个并发事务（2个或者以上）；每个事务都持有锁（或者是已经在等待锁）; 每个事务都需要再继续持有锁；事务之间产生加锁的循环等待，形成死锁。

避免死锁：

1）类似的业务逻辑以固定的顺序访问表和行。 2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。 3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。 4）降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择 5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁（或者说是表锁）