提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

mysql之事务 锁（三）

mysql事务
2023-06-13 09:13:58 时间

目录

事务

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

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

mysql中如何开启事务：

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

示例：

--查看事务自动提交状态
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 编程：

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

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

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

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

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

隔离级别的实现：锁 MVCC

锁

表锁 行锁

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

Innodb锁类型

用到的表：

-- ----------------------------
-- 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锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改; 加锁释锁方式：

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

释放方式：

commit/rollback

示例：

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


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

排它锁 Exclusive Locks

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

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

释锁方式：

commit/rollback

示例：

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;
-- 其他事务执行
--等待
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

----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;

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

----- 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的数据锁住 行锁级别 其他数据事务操作成功执行

--------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 当事务想去进行锁表时，可以先判断意向锁是否存在，存在时则可快速返回该表不能 启用表锁

----- 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永久丢失

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());

行锁算法

用到的表：

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锁住的区间和下一个区间

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事务级别存在

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

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）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁（或者说是表锁）

利用锁解决事务问题

解决脏读

加上排它锁（X锁）后没有 查询的时候处于等待状态 从而解决了脏读问题

解决不可重复读

加入共享锁 其他事物不能进行更新操作 因此解决了不可重复读问题

解决幻读

数据只有一条 区间分成了负无穷到16 16到正无穷 临键锁锁住了负无穷到16和下一个区间16到正无穷 其他事物的新增和删除操作无法执行 查询结果还是1条 从而解决幻读问题

相关文章