MySQL的事务和锁

1. MySQL的事务和锁

1.1. 事务的四大特性ACID

https://blog.csdn.net/dengjili/article/details/82468576

原子性(Atomicity)

原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。

一致性(Consistency)

事务前后数据的完整性必须保持一致。

不能在事务执行后,违反了数据库的规范性约束。

规范性约束包含:

数据库的规范性约束,比如字段的唯一性。

用户自定义的规范性约束,比如余额要大于0。

隔离性(Isolation)

事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。

隔离性通过两种方式实现:

锁定(LBCC=Lock Based Concurrency Control),缺点:读数据不允许其他人修改。

快照(MVCC=Multi Version Concurrency Control)

持久性(Durability)

持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响

持久性一般通过redo log/undo log实现。

1.2. 事务的三大问题

1.2.1. P1:脏读

指一个事务读取了另外一个事务未提交的数据。

1.2.2. P2:不可重复读

在一个事务内读取表中的某一行数据,多次读取结果不同。

导致这种结果的原因:

1、另一个事务修改并提交了。

2、另一个事务删除并提交了。

1.2.3. P3:幻读

是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。

 

1.3. 事务隔离级别

1.3.1. SQL标准的四种隔离级别

set transaction isolation level 设置事务隔离级别
select @@tx_isolation 查询当前事务隔离级别

 

Serializable

可避免脏读、不可重复读、虚读情况的发生。(串行化)

Repeatable read

可避免脏读、不可重复读情况的发生。(可重复读)

Read committed

可避免脏读情况发生(读已提交)。

Read uncommitted

最低级别,以上情况均无法保证。(读未提交)

 

事实上,Serializable(性能差)和Read uncommitted(结果不可接受)基本不太可能出现在生产环境。

所以,一般在RC和RR中选择,更多的是选RR。

但根据定义,RR并不能解决幻读的问题。

1.3.2. MySql的隔离级别

MySql的默认数据库引擎是innoDB,

innoDB与标准的区别:RR已经可以避免所有三种问题。

 

1.3.3. innoDB隔离级别的实现原理

https://www.cnblogs.com/innobase/p/5419124.html

采用快照(MVCC=Multi Version Concurrency Control)的方式

 

为每个表添加隐藏字段:

DB_ROW_ID  行号(与MVCC无关)

DB_TRX_ID   事务ID,用于记录数据产生顺序

DB_ROLL_PTR 回滚指针,用户关联undo log

InnoDB中有运行期间有一个全局的事务链表,每个事务的开始都会把事务ID放到链表中。DB_ROLL_PTR指针用于指向undo 记录,构造多版本的。

 

(1)   select操作:

对于select的操作,只有同时满足如下2个条件才返回行记录

a)        行的修改版本号小于等于该事务版本号

b)        行的删除版本号要么没有被定义,要么大于事务版本号。

如果行的修改或者删除版本号大于事务号,说明行是被修改事务后启动的事务修改或者删除的。在可重复读的隔离级别下,后开始的事务堆数据的影响不应该被先开始的事务看见,所以应该忽略后开始的事务的更新或者删除操作。

 

(2)   insert操作:

新插入的行,行的修改版本号更新为该事务的事务号。

 

(3)   update操作:

更新行的时候,InnoDB会把原来行复制一份,并把当前的事务号作为改行的修改版本号。

 

(4)   delete操作:

对于删除,InnoDB直接把改行的删除版本号修改为当前事务号,相当于标记为删除,而不是物理的删除。真是的删除是在InnoDB的purge线程去做的。

1.4. innoDB的锁

1.4.1. 锁的分类

行锁:

共享锁:读锁。

排它锁:写锁。Update/insert/delete默认加排它锁。

表锁:意向锁:一般不需要自己操作,由数据库引擎实现,当行锁发生后会触发表锁标志。只是一个标志,用于更快的锁定失败。

意向共享锁

意向排它锁

1.4.2. 行锁的原理

锁住的是索引。

任何表都存在索引,即便任何索引都没有创建,都会有一个ROW_ID的默认索引。

聚簇索引:与物理存放一致的那个索引,B+树存放数据的所有信息,有主键时就是主键。

辅助索引:保存的是主键的值,通过索引找到主键值,再通过主键查找。

 

 

 

记录锁,精确指向记录时,锁定一条记录。  where id = 1 ; 锁定1

Gap:两个record间的一段区域。

Gap锁,未能精确指向记录时,锁定一段。   where id >8 and id< 9; 锁定(7,10)

Gap锁的目的是为了防止幻读。

临键锁,包含record的一段区域,锁定左开右闭区间。 where id > 5 and id <8,锁定(4,10]

 

 

当没有建索引,而使用rowid作为聚簇索引的时候,所有的锁定操作都会锁表。

 

posted @ 2020-03-01 22:06  傲衣华少  阅读(183)  评论(0编辑  收藏  举报