一、锁(MyISAM 和 InnoDB)
1. MyISAM: 默认表级锁,不支持行锁。
select 读锁为共享锁。可以同时执行多个读的操作
update、insert、delete 等操作为写锁,写锁是排他锁。
手动加锁: lock tables table_name read/write;
为select 添加排他锁: select .................................. for update;
2. Innodb 默认行级锁,也支持表级锁。
表级锁:
- 表读写锁
读为共享锁,写为排他锁。 加表锁可以使用 lock tables/table table_name read/write;
命令show open tables,可以查看对应表锁的个数。
- 意向锁
这是InnoDB引擎实现的表级锁,是为了全表更新数据时或者加其他表锁时性能提升,避免全表遍历排查某行数据是否加了行锁,对加了行锁的表,加上意向锁标识;
意向锁分为意向共享锁(IS锁)和意向排他锁(IX锁),意向锁之间不互相排斥,只是作为表锁的标识,如表中多行记录可以既有共享锁和排他锁,那么导致表的意向锁就会既有IS锁和IX锁,这种意向锁就是为了给表加锁时可以更快的判断能加哪种锁。
- 自增锁
自增锁是一种特殊的表级别锁。它是专门针对AUTO_INCREMENT类型的列,对于这种列,如果表中新增数据时就会去持有自增锁。简言之,如果一个事务正在往表中插入记录,所有其他事务的插入必须等待,以便第一个事务插入的行,是连续的主键值。
行级锁:通过给索引的索引项加锁实现的,所以只有通过索引条件检索的数据,InnoDB才能加行锁,否则会对所有行或行间隙加锁,表现为使用了表锁。根据锁定范围分为记录锁(Record Locks)、间隙锁(Gap Locks)、临键锁(Next-Key Locks)和插入意向锁(Insert Intention Locks)。按照功能仍可以分为共享锁(S)和排他锁(X)。
innodb 会自动给insert、update、delete语句加X锁。但select不会加任何锁(非Serializable隔离等级),除非显式的加锁: select ...... lock in share mode (S锁) | select ...... for update (X锁)
- 记录锁
锁定索引中一条记录。其实锁住的是索引(如果是主键索引,就锁住主键索引,如果是辅助索引,则锁住辅助索引和主键索引),如果检索记录没有用到索引,那么就会将表的所有聚簇索引记录都锁定,表现为表锁;
- 间隙锁 (Gap)
是一种区间锁,锁住的是索引开区间,即不包括两端,也就是不包括索引记录本身;间隙锁可以防止幻读,即在当前事务增删改中,用到间隙锁后,其他事务便无法加上间隙记录的X锁,也就不能增删改间隙记录;
- 临键锁 (Next-Key) = 行锁 + Gap锁
锁定索引区间为左开右闭区间的锁。在 RR和Serializable级别下,会在当前读的操作中使用临键锁。InnoDB会根据不同SQL操作对临键锁进行优化:
当主键索引或唯一索引全部命中时临键锁会降级为记录锁,即锁住记录本身,而不是范围;
当主键索引或唯一索引部分命中时,行锁+Cap锁
当辅助索引等值查询且有记录时,临键锁会锁住等值记录的主键索引项和辅助索引项,同时会加间隙锁锁住辅助索引区间项
当范围匹配时,就使用临键锁,即Record Lock + Gap Lock。
- 插入意向锁
是一种Gap Lock,不是意向锁,在insert操作时产生;插入意向锁不会阻止任何锁,对插入的记录进行锁定,防止其他事务插入相同主键记录,而不同主键记录之间的插入互不影响。
二、事务
1. 数据库事务的四大特性 ACID
- 原子性 (Atomic):事务包含的所有操作,要么全部执行,要么全部失败回滚。
- 一致性(Consistency):事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态指数据库中的数据应满足完整性约束。
- 隔离性(Isolation):多个事务并发执行时,事物之间应该互不干扰。
- 持久性(Durability):一个事务一旦提交,它对数据库的修改应该是永久的。持久性意味着当系统或者介质发生故障时,确保已提交事务的更新不能丢失,即对已提交事务的更新能恢复。一旦一个事务被提交,DBMS(数据库管理系统)必须保证提供适当的冗余,使其耐得住系统的故障。所以持久性主要在于DBMS的恢复性能。例如 innoDB会将所有的数据库修改保存在二进制日志中。
2. 事务相关的SQL语句
- show variables like 'transaction_isolation'/select @@transaction_isolation; 查看当前session的隔离级别
- start transaction 开启事务
- commit 提交
- rollback 回滚
- set session transaction isolation level (read uncommitted)/ (read uncommitted)/ (repeatable read )/ (serializable)
3. 事务隔离级别以及各级别下的并发访问问题
3.1 事务隔离的4个级别
(1)读未提交——read uncommitted:在事务过程中,可以读取其他事务未提交的数据。
(2)读已提交——read committed: 在事务过程中,可以读取其他事务已提交的数据。
(3)可重复读——repeatable read:可重复读,事务中多次读取,数据一致。
(4)串行化——serializable:任何操作都会请求响应的锁。能否执行要看能否获取到操作行的锁。按照锁的兼容关系执行。
3.2 常见的几种并发问题
(1) 脏读: 事务A读取数据的过程中,能读取到其他事务修改但未提交的数据。 RC级别以上可以避免。
(2)不可重复读: 事务A多次读取同一条数据,取得的数据结果不一致。 RR级别以上可以避免。
(3)幻读: 当事务操作时,有其他事务插入或删除了数据,导致当前事务进行了错误操作。 serializable级别可以避免。
4. 当前读与快照读
对于RC和RR两种模式,在RC模式下,会读取最新的已提交数据。RR模式,会保证多次读取数据一致性,是否读取最新版本,要看建立快照的时间。
当前读:select...lock in share mode, select ... for update, update, delete, insert。 即加锁的操作都可以视为当前读。读取的是记录的最新版本,而且保证其它并发事务不能修改当前记录。
为什么 update,delete, insert等操作都称为当前读呢???以update为例,原因如下图所示:
快照读: 不加锁的非阻塞读,(非serializable级别的)select
InnoDB下数据行里除了数据字段还有一些隐藏字段,例如DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID等,这些就是实现快照读的关键。
- DB_TRX_ID: 最后一次对本行做修改的事务ID,每处理一个事务,其值自动加1
- DATA_ROLL_PTR 指向当前记录项的rollback segment的undo log记录,找之前版本的数据就是通过这个指针
- DB_ROW_ID,当由innodb自动产生聚集索引时,聚集索引包括这个DB_ROW_ID的值,否则聚集索引中不包括这个值,这个用于索引当中
- DELETE BIT位用于标识该记录是否被删除,这里的不是真正的删除数据,而是标志出来的删除,真正意义的删除是在commit的时候
当我们对一行数据进行修改时,innoDB会更新DB_TRX_ID 事务ID,并将更新前的行保存到undo日志中,然后DB_ROLL_PTR指向undo日志中的这一行。流程如下所示:
初始插入行: |
事务1修改: |
事务2修改: |
在RR级别下,事务开始时的第一次快照读会创建一个快照Read View 将系统中其他活跃的事务记录下来,此后当再次快照读的时候,仍使用同一个Read View。 RC级别下每次快照读都重新建立快照,这就是为什么RC模式读取的是最新提交的数据。
InnoDB在RR与Serializable级别下如何避免幻读? 临键锁!!!!临键锁会锁住记录本身和附近的区间,以防止幻读。例如A字段为int类型,且为普通索引,如下图所示。当事务1删除 A= 10的数据时,innoDB会锁定(7,10] 和(10,12]的区间。这时事务2想要插入A=9的数据,就会阻塞。