不在事务中能加锁吗_MySQL各种加锁案例分析

目录

InnoDB 锁类型

InnoDB MVCC简要

事务隔离级别

SQL加锁分析（主题）

死锁举例分析

InnoDB锁类型

InnoDB Lock Types

❖Shared and Exclusive Locks
❖Intention Locks
❖Record Locks
❖Gap Locks
❖Next-Key Locks
❖Insert Intention Locks
❖Auto-inc Locks
❖ Predicate Locks for Spatial Index（忽略）

InnoDB - S&X Locks

❖Row-Level Locking
❖S Locks (shared locks)
✓A shared (S) lock permits the transaction that holds the lock to read a row
✓Example: select * from xx where a=1 lock in share mode
❖X Locks ( Exclusive Locks)
✓An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.
✓Example:select * from xx where a=1 for update
❖S 和 S 兼容， X 和 S 互斥， X和X互斥

InnoDB - S&X Locks举例例

❖select * from t where i=1 lock in share mode

❖select * from t where i=1 for update

插⼊入篇：如何查看SQL加锁信息

❖进⾏如下设置可以看到此SQL持有锁信息
✓SET GLOBAL innodb_status_output=ON;
✓SET GLOBAL innodb_status_output_locks=ON;
✓5.6.16版本引⼊此参数，影响性能，线上慎开

插⼊入篇：查看SQL加锁信息

InnoDB - Intention Locks

❖InnoDB⽀持多粒度锁，允许⾏锁和表锁并存
❖Table-Level Locks
❖Intention shared Lock(IS) 意味着事务需要在表的⾏上⾯添加S锁，因此获取S锁之前需要获取IS锁
❖ Intention exclusive Lock（IX）意味着事务需要在表的⾏上⾯添加X锁，因此获取X锁之前需要获取IX锁

InnoDB - 兼容性

InnoDB - Record Locks

❖记录锁是对索引加锁，⽽不是具体的数据⾏
❖即使表没有定义索引，InnoDB产⽣隐藏聚簇索引⽤于加锁

InnoDB - Gap Locks

❖A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.
❖A gap might span a single index value, multiple indexvalues, or even be empty.
❖Gap可能通过设置Read-Commited以及innodb_locks_unsafe_for_binlog进⾏显⽰关闭

InnoDB - Next-Key Locks

❖A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

❖InnoDB 默认的事务隔离级别是REPEATABLE READ
❖在RR模式下， InnoDB 使⽤ next-key locks 防⽌幻读

InnoDB - Next-Key Locks举例例1

InnoDB - Next-Key Locks举例例2

InnoDB - Insert Intention Locks

❖An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion.

InnoDB - Insert Intention Locks

InnoDB - AUTO-INC Locks

❖特殊的table-level lock
❖持有时间在当前sql执⾏完成就释放，⽽不是事务结束后才释放
❖通过参数innodb_autoinc_lock_mode控制，具体有三种模式：
AUTOINC_OLD_STYLE_LOCKING (0)
AUTOINC_NEW_STYLE_LOCKING (1) 保证Id连续，默认
AUTOINC_NO_LOCKING (2)

InnoDB - AUTO-INC Locks

AUTOINC_OLD_STYLE_LOCKING（0）
1)在分⽚片前加上AUTO_INC锁，并在SQL结束时释放掉

AUTOINC_NO_LOCKING （2）
1)只在分配时加个mutex即可很快就释放
2)在statement格式下不不能保证批量量插⼊入的复制安全性

InnoDB MVCC

数据库并发控制协议

1、基于锁的协议 (Lock Based Protocol)
2、基于多版本机制 (Multi-version Protocol)
3、基于时间的协议 (Time-Stamp Ordering Protocol)
4、基于图的协议 (Graph Based Protocol)
5、基于多粒度协议 (Multiple Granularity Protocol)

MVCC

❖多版本控制
❖相对于基于锁的协议，MVCC最⼤好处：读不加锁，读写不冲突
❖读操作
✓快照读(snapshot read)
✓当前读(current read)

快照读和当前读

❖快照读：简单select操作，不加锁
❖select * from table where ?
❖ 当前读: 特殊读操作，插⼊/更新/删除，需要加锁
❖select * from table where ? lock in share mode
❖select * from table where ? for update
❖insert into table values (…)
❖delete from table where ?
❖update table set ? where ?

InnoDB MVCC实现关键点

❖ROW记录格式
❖ROW和Undo关系
❖ReadView判断

InnoDB MVCC实现原理理-Row格式

实际上还有⼀个字段DB_ROW_ID，只出现在聚簇索引中

InnoDB MVCC实现原理理-Row和Undo

❖通过回滚段实现多个版本的读取

InnoDB MVCC实现原理理-Read View

InnoDB MVCC实现原理理-可⻅见性判断

事务隔离级别

读现象问题

❖丢失更新 （事务ACID保证不会发⽣）
❖脏读
❖不可重复读
❖幻读

事务隔离级别

❖Read Uncommitted
✓可以读取未提交的事务，此隔离级别不会使⽤。
❖Read committed (RC)
✓针对当前读，RC隔离级别保证对读取到的记录加锁（⾏锁），存在幻读现象。
❖Repeatable Read(RR)
✓针对当前读，RR保证对读取到的记录加锁（⾏锁），同时保证对读取的范围加锁，新的满⾜查询条件的记录不能够插⼊（Next-Key Locks），不存在幻读现象。
❖Serializable
✓从MVCC并发控制退化为基于锁的并发控制。所有的读操作都为当前读，读加读锁（S锁），写加写锁（X锁）。Serializable隔离级别下，读写冲突，并发度急剧下降。

读现象和事务隔离级别举例例

读现象举例例-脏读

❖脏读：当⼀个事务允许读取另⼀个事务修改但未提交的数据时，就可能发⽣脏读。

读现象举例例-不不可重复读

❖不可重复读：在⼀次事务中，当⼀⾏数据获取两遍得到不同的结果表⽰发⽣了“不可重复读”

读现象举例例-幻读

❖ 幻读：不可重复读的⼀种特殊场景。当事务1两次执⾏SELECT ... WHERE检索⼀定范围内数据的操作中间，事务2在这个表中创建了(如INSERT)⼀⾏新数据，这条新数据正好满⾜事务1的“WHERE”⼦句。

标准事务隔离级别

❖标准事务隔离级别中RR没有解决幻读问题。

InnoDB在RR模式解决幻读问题举例例

SQL加锁分析

❖简单SQL加锁分析
❖复杂SQL加锁分析
❖常见SQL加锁总结

Update操作加锁流程

2PL：Two-Phase Locking

一条简单SQL的加锁分析-问题

❖SQL1：select * from t1 where id = 10
✓MVCC多版本控制，Select 快照读，不加锁
❖SQL2: delete from t1 where id = 10

一条简单SQL的加锁分析-前提条件

❖前提⼀：id列是不是主键？
❖前提⼆：当前系统的事务隔离级别是什么？
❖前提三：id列如果不是主键，那么id列上是否有索引吗？
❖前提四：id列上如果有⼆级索引，那么这个索引是唯⼀索引吗?
❖前提五：SQL的执⾏计划是什么？索引扫描？全表扫描？

SQL加锁分析前提场景

❖组合⼀：id列是主键，RC事务隔离级别
❖组合⼆：id列是⼆级唯⼀索引，RC事务隔离级别
❖组合三：id列是⼆级⾮唯⼀索引，RC事务隔离级别
❖组合四：id列上没有索引，RC事务隔离级别
❖组合五：id列是主键，RR事务隔离级别
❖组合六：id列是⼆级唯⼀索引，RR事务隔离级别
❖组合七：id列是⼆级⾮唯⼀索引，RR事务隔离级别
❖组合⼋：id列上没有索引，RR事务隔离级别
❖组合九：Serializable事务隔离级别

组合⼀一：id主键+RC

组合⼆二：id唯⼀一索引+RC

复杂SQL加锁规则

声明

作者：一个人的Code
链接： https:// blog.csdn.net/weixin_41 850404/article/details/84653909
来自：csdn