mysql的事务与锁机制
2020-11-15 本文已影响0人
suxin1932
MVCC机制
死锁
1.关系型数据库遵循ACID原则:
事务在英文中是transaction，和现实世界中的交易很类似，它有如下四个特性：
#1、A (Atomicity) 原子性
原子性很容易理解，也就是说事务里的所有操作要么全部做完，要么都不做，
事务成功的条件是事务里的所有操作都成功，只要有一个操作失败，整个事务就失败，需要回滚。
#比如银行转账，从A账户转100元至B账户，分为两个步骤：
1）从A账户取100元；
2）存入100元至B账户。
这两步要么一起完成，要么一起不完成，如果只完成第一步，第二步失败，钱会莫名其妙少了100元。
#2、C (Consistency) 一致性
一致性也比较容易理解，也就是说数据库要一直处于一致的状态，
事务的运行不会改变数据库原本的一致性约束。
#例如现有完整性约束a+b=10，
如果一个事务改变了a，那么必须得改变b，使得事务结束后依然满足a+b=10，否则事务失败。
#3、I (Isolation) 独立性
所谓的独立性是指并发的事务之间不会互相影响，
如果一个事务要访问的数据正在被另外一个事务修改，
只要另外一个事务未提交，它所访问的数据就不受未提交事务的影响。
#比如现在有个交易是从A账户转100元至B账户，
在这个交易还未完成的情况下，如果此时B查询自己的账户，是看不到新增加的100元的。
#4、D (Durability) 持久性
持久性是指一旦事务提交后，
它所做的修改将会永久的保存在数据库上，即使出现宕机也不会丢失。
1.1 并发事务处理带来的问题
相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，
提高数据库系统的事务吞吐量，从而可以支持更多的用户。
但并发事务处理也会带来一些问题，主要包括以下几种情况:
更新丢失（Lost Update）
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，
就会发生丢失更新问题, 最后的更新覆盖了由其他事务所做的更新。
例如，两个编辑人员制作了同一文档的电子副本。
每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。
最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。
如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。
#第一类更新丢失:
张三的工资为5000，
事务A中获取工资为5000，事务B获取工资为5000，
事务A与B各汇入100，并提交数据库，工资变为5200，
随后
事务A发生异常，回滚了，恢复张三的工资为5000，这样就导致事务B的更新丢失了。
#第二类更新丢失(不可重复读的特例):
在事务A中，读取到张三的存款为5000，操作没有完成，事务还没提交。
与此同时，
事务B，存储1000，把张三的存款改为6000，并提交了事务。
随后，
在事务A中，存储500，把张三的存款改为5500，并提交了事务，
这样事务A的更新覆盖了事务B的更新。
脏读（Dirty Reads）
一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；
这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，
并据此做进一步的处理，就会产生未提交的数据依赖关系。
这种现象被形象地叫做"脏读"。
张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。
与此同时，
事务B正在读取张三的工资，读取到张三的工资为8000。
随后，
事务A发生异常，而回滚了事务。张三的工资又回滚为5000。
最后，
事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。
不可重复读（Non-Repeatable Reads）
一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，
却发现其读出的数据已经发生了改变、或某些记录已经被删除了！
这种现象就叫做“不可重复读”。
在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。
与此同时，
事务B把张三的工资改为8000，并提交了事务。
随后，
在事务A中，再次读取张三的工资，此时工资变为8000。
在一个事务中前后两次读取的结果并不致，导致了不可重复读。
幻读（Phantom Reads）
一个事务按相同的查询条件重新读取以前检索过的数据，
却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。
此时，
事务B插入一条工资也为5000的记录。
这时候，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。
1.2 事务隔离级别-->并发事务的问题
更新丢失:
不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，
因此，防止更新丢失应该是应用的责任。
“脏读”、“不可重复读”和“幻读”:
其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。
数据库实现事务隔离的方式，基本上可分为以下两种。
1)一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
2)另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，
并用这个快照来提供一定级别（语句级或事务级）的一 致性读取。
从用户的角度来看，好像是数据库可以提供同一数据的多个版本，
因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），
也经常称为"多版本数据库"。
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，
因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。
同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，
比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了4个事务隔离级别
| 隔离级别 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| 未提交读(Read uncommitted) | 最低级别，只能保证不读取物理上损坏的数据 | 是 | 是 | 是 |
| 已提交度(Read committed) | 语句级 | 否 | 是 | 是 |
| 可重复读(Repeatable read) | 事务级 | 否 | 否 | 是 |
| 可序列化(Serializable) | 最高级别，事务级 | 否 | 否 | 否 |
"Oracle"
只提供Read committed和Serializable两个标准隔离级别，
另外还提供自己定义的Read only隔离级别；
"oracle默认的事务处理级别:
是read_committed"
"SQL Server"
除支持上述4个隔离级别外，还支持一个叫做“快照”的隔离级别，
但严格来说它是一个用MVCC实现的Serializable隔离级别;
"MySQL"
支持全部4个隔离级别，但在具体实现时，
有一些特点，比如在一些隔离级别下是采用MVCC一致性读，但某些情况下又不是.
"mysql默认的事务处理级别:
是'REPEATABLE-READ',也就是可重复读,
但仍可能导致更新丢失, 该问题可由应用程序解决
"
1.2.1 前置背景
假设有如下表结构的数据.
| id | first_name | last_name | gender | age | address | phone |
|---|---|---|---|---|---|---|
| 1 | 张 | 三 | 男 | 20 | nj | 12345678901 |
| 2 | 李 | 四 | 男 | 30 | bj | 12345678902 |
| 3 | 王 | 五 | 男 | 40 | tj | 12345678903 |
1.2.2 事务的可见性
>> 事务一定能看到自己的修改
>> 事务可能看得到已提交的数据
>> 事务可能看得到未提交的数据
| 连接1 | 连接2 |
|---|---|
| INSERT INTO stu VALUES('赵', '六', '女', 50, 'gz', '12345678904'); | |
| START TRANSACTION; | |
| START TRANSACTION; | SELECT * FROM stu WHERE phone='12345678904'; (能看得到刚才插入的'赵六') |
| INSERT INTO stu VALUES('于', '七', '女', 60, 'sc', '12345678905'); | DELETE FROM stu WHERE first_name='赵' AND last_name='六'; |
| SELECT * FROM stu WHERE phone>='12345678904'; ('赵六'已经被删掉了, 同时'于七'也读不到) |
|
| COMMIT; | COMMIT; |
1.2.3 事务的可见性-Serilizable
事务需要对读到的数据进行加锁
(这种级别影响性能, 慎用)
| 连接1 | 连接2 |
|---|---|
| SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION; |
|
| START TRANSACTION; | SELECT * FROM stu; (读到三条记录) |
| INSERT INTO stu VALUES('赵', '六', '女', 50, 'gz', '12345678904'); (等待, 语句无法执行) |
|
| SELECT * FROM stu; (还是读到三条记录, 因为'赵六'还没插入) |
|
| COMMIT; | |
| 连接2COMMIT之后, INSERT语句执行成功; COMMIT; |
|
| SELECT * FROM stu; (读到四条记录, 包含'赵六') |
1.2.4 事务的可见性-Repetable Read
事务看到的始终是本事务第一次读时候能看到的内容
1.2.4.1 事务的可见性-Repetable Read(1)
| 连接1 | 连接2 |
|---|---|
| START TRANSACTION; | |
| START TRANSACTION; | SELECT * FROM stu; (读到三条记录) |
| INSERT INTO stu VALUES('赵', '六', '女', 50, 'gz', '12345678904'); (等待, 语句无法执行) |
|
| COMMIT; | SELECT * FROM stu; (还是读到三条记录, 因为连接2还没commit) |
| SELECT * FROM stu; (还是读到三条记录, 因为连接2还没commit) |
|
| COMMIT; | |
| SELECT * FROM stu; (读到四条记录, 包含'赵六') |
1.2.4.2 事务的可见性-Repetable Read(2)
| 连接1 | 连接2 |
|---|---|
| START TRANSACTION; | |
| START TRANSACTION; | SELECT * FROM stu; (读到三条记录) |
| DELETE FROM stu WHERE phone='12345678902' | |
| UPDATE stu SET phone='86-12345678901' WHERE phone='12345678901' | SELECT * FROM stu; (还是读到三条记录, 因为连接2还没commit) |
| COMMIT; | SELECT * FROM stu; (还是读到三条记录, 因为连接2还没commit) |
| SELECT * FROM stu; (还是读到三条记录, 因为连接2还没commit) |
|
| COMMIT; | |
| SELECT * FROM stu; (读到两条记录, 其中一条被删除) |
1.2.5 事务的可见性-Read Committed
事务看到的始终是每个读开始时刻已提交的数据
1.2.5.1 事务的可见性-Read Committed (1)
| 连接1 | 连接2 |
|---|---|
| SET TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; |
|
| START TRANSACTION; | SELECT * FROM stu; (读到三条记录) |
| INSERT INTO stu VALUES('赵', '六', '女', 50, 'gz', '12345678904'); (等待, 语句无法执行) |
|
| COMMIT; | |
| SELECT * FROM stu; (读到四条记录, 包含连接1插入的数据) |
|
| COMMIT; |
1.2.5.2 事务的可见性-Read Committed (2)
| 连接1 | 连接2 |
|---|---|
| SET TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; |
|
| START TRANSACTION; | SELECT * FROM stu; (读到三条记录) |
| DELETE FROM stu WHERE phone='12345678902' | |
| UPDATE stu SET phone='86-12345678901' WHERE phone='12345678901' | |
| SELECT * FROM stu; (还是读到三条记录, 因为连接1还没commit) |
|
| COMMIT; | |
| SELECT * FROM stu; (读到四条记录, 包含连接1插入的数据) |
|
| COMMIT; |
1.2.5.3 事务的可见性-Read Committed (3)
| 连接1 | 连接2 |
|---|---|
| SET TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; |
|
| START TRANSACTION; | SELECT * FROM stu; (读到三条记录) |
| DELETE FROM stu WHERE phone='12345678902' | |
| UPDATE stu SET phone='86-12345678901' WHERE phone='12345678901' | |
| SELECT * FROM stu; (还是读到三条记录, 因为连接1还没commit) |
|
| ROLLBACK; | |
| SELECT * FROM stu; (还是读到三条记录, 因为连接1rollback了) |
|
| COMMIT; |
1.2.6 事务的可见性-Read Uncommitted
事务看得到当前最新的未提交数据
| 连接1 | 连接2 |
|---|---|
| SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; START TRANSACTION; |
|
| START TRANSACTION; | SELECT * FROM stu; (读到三条记录) |
| UPDATE stu SET phone='86-12345678901' WHERE phone='12345678901' | |
| SELECT * FROM stu; (读到连接1的1条更新) |
|
| UPDATE stu SET phone='86-12345678902' WHERE phone='12345678902' | |
| SELECT * FROM stu; (读到连接1的2条更新) |
|
| COMMIT; | COMMIT; |
1.3 事务的优化思路
1.3.1 避免大事务和长事务
#1.避免大事务
>> 主要指事务包含的语句很多，或者语句执行耗时很长
>> 将大事务转换为若干小事务提交，提高可靠性
>> 优化大事务逻辑 ， 如删除全表数据改为 TRUNCATE TABLE
>> 注意DDL执行的耗时，以及它对资源、复制等其它问题的影响
#2.避免长事务
>> 主要指事务目前不繁忙，但是一直没有提交
>> 长事务占用连接资源
>> 长事务可能占用系统资源，如磁盘空间等
>> 长事务可能导致过期数据一直无法回收
1.3.2 优化小事务
>> 频繁的单语句DML事务不利于性能
>> 考虑将可以合并的DML放在一个事务提交
>> 多条语句合并为一条语句(比如批量更新或批量插入)
1.3.3 事务隔离级别的选择
确认隔离级别对并发DML的影响.
>> 最常用的隔离级别是REPETABLE READ和READ COMMITED;
>> 另外两种隔离级别慎用.
1.3.4 其他优化
#1.确定是不是能用只读事务
>> START TRANSACTION READ ONLY;
>> 可以提高性能
#2.索引对加锁的影响
>> 如果表上没有索引, 一旦涉及到范围加锁, 可能整张表都被锁住
>> 如果表上有唯一索引, 唯一索引的加锁粒度更小
>> 如果使用二级索引扫描进行更新, 二级索引和聚簇索引记录都要加锁
1.4 事务的生命周期
#1.事务一般有三种开启方式
>> BEGIN/START TRANSACTION;
>> AUTOCOMMIT=0;
>> AUTOCOMMIT=1; 一条语句即一个事务
#2.事务的结束一般有四种方式
>> COMMIT: 所有的修改都会生效
>> ROLLBACK: 所有的修改都会回滚
>> 当前连接断开, 事务会回滚
>> 执行某些特定的DDL语句, 原有事务会被隐式提交, 之后才执行DDL
2.mysql中的锁问题
2.1 InnoDB引擎支持行锁及表锁
数据库系统使用锁机制来支持事务的并发控制和隔离性.
在mysql 的 InnoDB引擎支持行锁，分布式存储引擎NDBCluster也是
与Oracle不同，mysql的行锁是通过索引加载的，即是行锁是加在索引相应的行上的，
要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁。
2.1.1 表锁/意向锁
不会出现死锁，发生锁冲突几率高，并发低。
显式的表锁
#有两种类型
>> READ 持有者只能读加锁的表, 不同会话可以共同持有表锁
>> WRITE 只有持有者可以读写加锁的表, 其他会话都不能访问加锁的表
#语法
LOCK TABLES t1 READ [, t2 READ [, t3 WRITE]] ...;
UNLOCK TABLES;
#特点
>> 所有当前会话要访问表需要在同一个LOCK TABLES语句里面加锁
>> 加锁语句会隐式的提交当前未完成的事务
>> 加锁语句会隐式的释放当前已持有的表锁
#缺点
加锁粒度太大, 不利于并发, 谨慎使用
显式的表锁.png
隐式的表锁
隐式的表锁一般对用户不可见, 用户不可操作, 但能感知到, 主要用于数据库内部并发同步保持正确性.
有以下两种情况
| 连接1 | 连接2 |
|---|---|
| ALTER TABLE stu ADD COLUM hobby VARCHAR(128) DEFAULT 'None', ALGORITHM=COPY; | |
| --executing... | SELECT * FROM stu; (读到三条记录, 不会看到hobby字段, 没有阻挡) |
| --executing... | INSERT INTO stu VALUES('赵', '六', '女', 50, 'gz', '12345678904'); (等待, 语句无法执行) |
| --ALTER TABLE结束 | -- 紧随ALTER TABLE结束之后, 上面的插入语句也返回执行失败, 列的个数不匹配 |
| 连接1 | 连接2 |
|---|---|
| ALTER TABLE stu ADD COLUM hobby VARCHAR(128) DEFAULT 'None', ALGORITHM=INPLACE; 注意这里的参数是 'INPLACE' |
|
| --executing... | SELECT * FROM stu; (读到三条记录, 不会看到hobby字段, 没有阻挡) |
| --executing... | INSERT INTO stu VALUES('赵', '六', '女', 50, 'gz', '12345678904'); (等待, 语句无法执行) |
| --ALTER TABLE结束 | SELECT * FROM stu; (读到四条记录, hobby字段都为'None') |
2.1.2 行锁
行锁主要存在于InnoDB存储引擎层.
会出现死锁，发生锁冲突几率低，并发高。
#行锁的主要类型:
>> 记录锁
>> 间隙锁
>> 插入意向锁
>> ...
#行锁模式
>> 共享锁
>> 互斥锁
#加锁语句
>> DML语句
>> SELECT 语句, 带加锁提示
>> ...
2.1.2.1 行锁的类型
行锁分 共享锁 和 排它锁。
而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，
InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了"意向共享锁"和"意向排他锁"这两种
"共享锁/读锁"
当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，
但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。
"上共享锁的写法：lock in share mode
例如： select math from zje where math>60 lock in share mode；"
"排它锁/写锁"
当一个事务对某几个上写锁时，不允许其他事务写，但允许读。
更不允许其他事务给这几行上任何锁。包括写锁。
"上排它锁的写法：for update
例如：select math from zje where math >60 for update；"
2.1.2.2 行锁的实现
注意几点：
1.行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
2.两个查询事务不能锁同一个索引，例如：
"事务A先执行：
select math from zje where math>60 for update;
事务B再执行：
select math from zje where math<60 for update；
这样的话，事务B是会阻塞的。如果事务B把 math索引换成其他索引就不会阻塞，
但注意，换成其他索引锁住的行不能和math索引锁住的行有重复。"
3.insert ，delete ， update在事务中都会自动默认加上排它锁
对于普通SELECT语句，InnoDB不会加任何锁；
2.1.3 意向锁/表锁与行锁共存
"当一个事务需要给自己需要的某个资源加锁的时候"
"行锁的作用是"
如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。
如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。
"而意向锁的作用是"
当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。
如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。
如果自己需要的是某行(或者某些行)上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。
意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。
所以，可以说InnoDB的锁定模式实际上可以分为四种：
共享锁(S)
排他锁(X)
意向共享锁(IS)
意向排他锁(IX)
"意向锁是InnoDB自动加的，不需用户干预。"
| 当前锁模式/是否兼容/请求锁模式 | X(排它锁) | IX(意向排它锁) | S(共享锁) | IS(共享锁) |
|---|---|---|---|---|
| X(排它锁) | 冲突 | 冲突 | 冲突 | 冲突 |
| IX(意向排它锁) | 冲突 | 兼容 | 冲突 | 兼容 |
| S(共享锁) | 冲突 | 冲突 | 兼容 | 兼容 |
| IS(共享锁) | 冲突 | 兼容 | 兼容 | 兼容 |
2.1.4 MyISAM引擎支持表锁
MyISAM:
在执行查询语句(select)前, 会自动给涉及的所有表加读锁，
在执行增删改操作前, 会自动给涉及的表加写锁。
MySQL的表级锁有两种模式:
表共享读锁
表独占写锁
结论:读锁会阻塞写，写锁会阻塞读和写
"对MyISAM表的读操作"
不会阻塞其它进程对同一表的读请求，但会阻塞对同一表的写请求。
只有当读锁释放后，才会执行其它进程的写操作。
"对MyISAM表的写操作"
会阻塞其它进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。
MyISAM不适合做写为主表的引擎，
因为写锁后，其它线程不能做任何操作，
大量的更新会使查询很难得到锁，从而造成永远阻塞
2.1.5 一致性读和加锁读
InnoDB实现了两种不同的读数据机制
#1.一致性不加锁读
>> 不加锁, 基于多版本机制(MVCC)
>> 读写可并行
>> 读取的是指定时间点的快照内容, 不一定是最新内容
#2.加锁读
>> 读取的是最新数据
>> 基于锁管理机制, 按要求加锁, 锁冲突需要等待
>> 可能产生死锁
>> SELECT ... LOCK IN SHARE MODE;
>> SELECT ... FOR UPDATE;
2.2 锁冲突
例如说事务A将某几行上锁后，事务B又对其上锁，锁不能共存否则会出现锁冲突。
(但是共享锁可以共存，共享锁和排它锁不能共存，排它锁和排他锁也不可以)
2.2.1 锁冲突1
INSERT 和 DELETE可能会冲突, 例如先INSERT再DELETE场景
| 连接1 | 连接2 |
|---|---|
| START TRANSACTION; | START TRANSACTION; |
| INSERT INTO stu VALUES('赵', '六', '女', 50, 'gz', '12345678904'); (等待, 语句无法执行) |
|
| SELECT * FROM stu; (读到三条记录, 因为还没提交) |
|
| DELETE FROM stu WHERE phone>'12345678902' --等待, 无法立即执行, 返回 |
|
| COMMIT/ROLLBACK; | |
| --1.如果连接1是COMMIT, 则phone='12345678903'和'12345678904'两条记录都会被删掉 --2.如果连接1是ROLLBACK, 则只会删掉phone='12345678903' --3.如果连接1一直不提交, 则报超时错误 |
|
| COMMIT; |
2.2.2 锁冲突2
INSERT 和 DELETE可能会冲突, 例如先DELETE再INSERT场景
| 连接1 | 连接2 |
|---|---|
| START TRANSACTION; | START TRANSACTION; |
| DELETE FROM stu WHERE phone='12345678902' | |
| SELECT * FROM stu; (读到三条记录, 因为还没提交) |
|
| INSERT INTO stu VALUES('赵', '六', '女', 50, 'gz', '12345678902'); --等待, 无法立即执行, 返回 |
|
| COMMIT/ROLLBACK; | |
| --1.如果连接1是COMMIT, 则插入成功 --2.如果连接1是ROLLBACK, INSERT会报唯一主键冲突错误 --3.如果连接1一直不提交, 则报超时错误 |
|
| COMMIT; |
2.2.3 锁冲突3
INSERT 和 INSERT 可能会冲突
| 连接1 | 连接2 |
|---|---|
| START TRANSACTION; | START TRANSACTION; |
| INSERT INTO stu VALUES('赵', '六', '女', 50, 'gz', '12345678904'); | |
| SELECT * FROM stu; (读到三条记录, 因为还没提交) |
|
| INSERT INTO stu VALUES('赵', '六', '女', 50, 'gz', '12345678904'); --等待, 无法立即执行, 返回 |
|
| COMMIT/ROLLBACK; | |
| --1.如果连接1是COMMIT, 则INSERT会报唯一主键冲突错误 --2.如果连接1是ROLLBACK, 则插入成功 --3.如果连接1一直不提交, 则报超时错误 |
|
| COMMIT; |
2.2.4 锁冲突4
INSERT 和 SELECT可能会冲突, 例如先读后插入的场景
| 连接1 | 连接2 |
|---|---|
| START TRANSACTION; | |
| SELECT * FROM stu LOCK IN SHARE MODE; | |
| INSERT INTO stu VALUES('赵', '六', '女', 50, 'gz', '12345678904'); --等待, 无法立即执行, 返回 |
|
| COMMIT; | |
| --1.如果连接2很快COMMIT, 则INSERT成功 --2.如果连接1一直不提交, 则报超时错误 |
2.2.5 锁冲突5
INSERT 和 SELECT可能会冲突, 例如先插入后读的场景
| 连接1 | 连接2 |
|---|---|
| START TRANSACTION; | SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION; |
| INSERT INTO stu VALUES('赵', '六', '女', 50, 'gz', '12345678904'); | |
| SELECT * FROM stu; --锁等待, 无法立即执行, 返回 |
|
| COMMIT; | |
| --1.如果连接1很快COMMIT, 则能查到4条数据 --2.如果连接1一直不提交, 则报超时错误 |
2.3 死锁
例如说两个事务，事务A锁住了1~5行，同时事务B锁住了6~10行，
此时事务A请求锁住6~10行，就会阻塞直到事务B施放6~10行的锁，
而随后事务B又请求锁住1~5行，事务B也阻塞直到事务A释放1~5行的锁。
"死锁发生时，会产生Deadlock错误。"
"锁是对表操作的，所以自然锁住全表的表锁就不会出现死锁。"
2.3.1 避免死锁
死锁导致事务回滚，降低系统效率，浪费系统资源，影响业务体验
#1.最主要的原则是避免死锁条件的满足
>> 事务尽量小，比如只更新一条记录，但不代表不会死锁
>> 事务尽量短，缩短或者避免冲突时间窗
>> 事务更新多张表时，用同一个顺序更新不同的表
>> 事务更新一张表内的多行时，用同一个顺序更新不同的行
#2.另一个角度是减少事务的加锁
>> 避免事务（长时间）锁一个范围
>> 如果一致性读可以满足要求，尽量少用加锁读
>> 需要加锁读的时候，尽童使用READ COMMITTED隔离级别有利于减少死锁的产生
>> 使用索引扫描，减少事务加锁的数量
#3.如何监测和处理死锁
>> 应用程序做好重新启动事务的准备，应对死锁场景
>> SHOW ENGINE INNODB STATUS; / 错误日志
>> 根据死锁信息，调整应用程序逻辑
2.3.2 死锁检测
#1.mysql官网对死锁的说明
A deadlock is a situation where different transactions are unable to proceed because each holds a lock that the other needs. Because both
transactions are waiting for a resource to become available, neither ever release the locks it holds.
#2.死锁发生条件
>> 多个事务并发
>> 每个事务持有部分资源（行锁），需要申请更多的资源（行锁）
>> 一旦申请存在相互依赖，资源等待构成环，即形成死锁
#3.死锁检测
>> MySQL/InnoDB内部默认会进行死锁检测，避免事务长时间等待
>> 一旦检测到死锁，选择一个事务进行回滚，其它事务可以继续
#4.禁用死锁检测
>> 某些场景下，可以提高性能
>> 通过 innodb_ lock_ wait_ timeout 来控制死锁超时时间
2.3.3 死锁检测
UPDATE语句导致的死锁检测和处理
| 连接1 | 连接2 |
|---|---|
| START TRANSACTION; | START TRANSACTION; |
| UPDATE stu SET phone='86-12345678901' WHERE phone='12345678901' --更新成功 |
|
| UPDATE stu SET phone='86-12345678903' WHERE phone='12345678903' --更新成功 |
|
| SELECT * FROM stu WHERE phone='12345678903' FOR UPDATE; --等待, 因为该上被连接2更新了 |
|
| SELECT * FROM stu WHERE phone='12345678901' FOR UPDATE; --检测出死锁, 当前事务被回滚 |
|
| COMMIT; | COMMIT; --无效, 当前事务已被回滚 |
| SELECT * FROM stu; 只能看到连接1更新的那条记录和另外两条未曾改变的记录 |
2.4 乐观锁与悲观锁
乐观锁
使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。
何谓数据版本？
即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。
当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。
当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，
如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据
1.设计tb_test表有id, money, version三个字段
2.更新时:
1) 先读tb_test表的数据, 得到 id=id1, version=v1
select id, name, money from tb_test
2) 每次更新task表中的value字段时, 为了防止发生冲突, 需要这样操作
update tb_test set money=newMoney,version=v1+1 where id=id1 and version=v1
成功, 则成功, 失败则表明失败.
悲观锁
使用命令设置MySQL为非autocommit模式：
set autocommit=0;
设置完autocommit后，我们就可以执行我们的正常业务了。
需要注意的是，在事务中，
只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE同一笔数据时会等待其它事务结束后才执行，
一般SELECT ... 则不受此影响。
拿上面的实例来说，
当执行select name from tb_test where id=1 for update;后,
在另外的事务中如果再次执行select status from t_goods where id=1 for update;
则第二个事务会一直等待第一个事务的提交，此时第二个查询处于阻塞的状态，
但是如果我是在第二个事务中执行select status from t_goods where id=1;
则能正常查询出数据，不会受第一个事务的影响。
关于事务的处理的应用层/业务代码控制, 可参看<<spring系列>>文章
