2.1 并发带来的问题
- 更新丢失(脏写):对于同一行数据,一个事务对该行数据的更新操作覆盖了其他事务对该行数据的更新操作
- 脏读:一个事务读取了另一个事务未提交的数据,解决办法:写完之后再读
- 不可重复读:同一个事务中,使用相同的查询语句,在不同时刻读取到的结果不一致,解决办法:先读后写
- 幻读:同一个事务中,前后两次读取同一个范围内数据的记录数不一致,解决办法:先读后写
2.2 幻读和不可重复读的区别
- 不可重复读重点在更新和删除,幻读的重点在插入
- 在可重复读隔离级别中,SQL语句第一次读取到数据后,会将相应的数据加锁,使得其他事务无法修改和删除这些数据,此时可以实现重复读
- 幻读可以通过行锁来避免,但是会降低性能
2.3 事务隔离级别
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交Read Uncommit | 可能 | 可能 | 可能 |
读已提交Read Commit | 不可能 | 可能 | 可能 |
可重复读Repeatable Read | 不可能 | 不可能 | 可能 |
串行化Serialiable | 不可能 | 不可能 | 不可能 |
Oracle默认读已提交
MySQL默认可重复读
可重复读隔离级别使用了MVCC,数据库查询操作不会更新版本号,是快照读,而操作数据表的数据(insert,update,delete)则会更新版本号,是当前读。
解决幻读可以使用串行化隔离级别,或者间歇锁,临键锁
2.4 MYSQL中的锁
- 悲观锁:在整个数据处理过程中,会将相应的数据锁定
- 乐观锁:对数据库中的读写持乐观态度,通过数据库版本号机制实现;常用做法是在数据库表中增加一个version字段,查询数据时会将version字段一起读取出来,更新数据时,会令version字段值+1;提交数据时,将提交数据的版本与数据表对应记录的版本进行对比,如果大于要修改的数据版本号,对数据进行修改操作,否则,不修改数据表中的数据。
- 读锁:又称为共享锁或S锁(Shared Lock),针对同一份数据,可以加多个读锁而互不影响
- 写锁:又称为排它锁或X锁(Exclusive Lock),如果当前锁未释放,会阻塞其他的写锁和读锁
- 表锁:在整个数据表上对数据进行加锁和释放锁;当一个线程获取到一个表的读锁后,其他线程仍可以对表进行读操作,但是不能对表进行写操作;当一个线程获取到一个表的写锁后,只有持有锁的线程可以对表进行更新操作,其他线程的对表的读写都会被阻塞,直到写锁被释放;
锁表基本的操作:
-- 对表table_a增加读锁
lock table table_a read;
-- 对表table_a增加写锁
lock table table_a write;
-- 查看表的锁
show open tables;
-- 删除表锁
unlock tables;
- 行锁:在数据行上对数据进行加锁,分为共享锁和排它锁;
行锁的注意事项:
1)行锁主要加在索引上,如果对非索引字段设置条件进行更新,行锁可能会变成表锁;
2)InnoDB的行锁是针对索引加锁,不是针对记录加锁,并且加锁的索引不能失效,否则可能会变成表锁;
3)锁定一行记录时,可以使用lock in share mode来指定共享锁,使用for update命令来指定排它锁,如:
select * from account_t where id=1 for update;
- 页面锁:在页面级别对数据进行加锁和释放锁
- 间歇锁:使用范围查询时,InnoDB会给符合条件的数据的索引项加锁,如果范围内没有记录,存在间歇(GAP),InnoDB会对间歇加锁。即对两个值之间的间歇加锁。
- 临键锁:行锁和间歇锁的组合
2.5 死锁
InnoDB采用了等待图(wait-for-graph)方法来检测死锁,如果发生了死锁,就会自动回滚事务
避免死锁的方式:
- 尽量让数据表中的数据查询通过索引来完成
- 合理设计索引,尽量缩小锁的范围
- 尽量减少查询条件范围,避免间歇锁和缩小间歇锁的范围
- 控制事务大小,减少一次事务锁定的资源数量,缩短锁定时间
- 如果一条SQL语句涉及到加锁操作,则尽量让其放在整个事务的最后执行
- 尽可能使用级别低的事务隔离机制
2.6 MVCC
多版本并发控制:每个连接到数据库的读操作,在某个瞬间看到的都是数据库中数据的一个快照,而写操作的事务提交之前,读操作是看不到这些数据的变化的。MVCC通过在每行数据表记录后面保存两个隐藏的version列来实现,一个用来保存行的创建版本号,一个用来保存行的过期版本号。
2.6.1 MVCC的增删改查
可重复读隔离级别下,MVCC的增删改查:
- select:InnoDB只会查找不晚于当前事务版本的数据行,即版本号小于等于当前事务版本号的数据行,保证事务读取的数据行要么之前就已经存在,要么是当前事务自身插入或修改的记录。另外,行的删除版本号要么未定义,要么大于当前事务的版本号,这样保证事务读取的行在事务开始之前没有被删除
- insert:将当前事务的版本号保存为当前行的创建版本号;
- delete:将当前事务的版本号保存为删除行的删除版本号,作为行删除标识;
- update:InnoDB将待修改的行复制为新的行,将当前事务的版本号保存为新数据行的创建版本号,同时保存当前事务的版本号为原来数据行的删除版本号
2.6.2 MVCC实现
未实现MVCC,InnoDB为每行数据后面增加了3列
- 6字节的事务ID字段(DB_TRX_ID)
用来标识最近一次对本行记录修改的事务ID
- 7字节的回滚指针字段(DB_ROLL_PTR)
指向上一个版本的行记录,能够从最新版本的行逐级向上,找到要查找的行的版本记录
- 6字节的DB_ROW_ID字段
包含一个随着插入操作而单调递增的id, 当由InnoDB引擎自动产生聚集索引时,聚集索引会包含这个id, 否则这个id不会出现在任何索引中
2.7 MYSQL的日志
2.7.1 Redo Log
用来保证事务的原子性和持久性,主要记录对磁盘数据的修改操作,可以恢复数据到最后一次提交的位置;在MySQL发生故障时,尽力避免内存中的脏页数据写入数据表的IDB文件中;在数据库重启时,可以根据Redo Log恢复事务已经提交但是还未写入IBD文件中的数据,从而对事务提交的数据进行持久化操作。
在InnoDB中,通过提交事务时强制执行写日志操作机制实现事务的持久化
2.7.2 Undo Log
用于回滚和多版本并发控制(MVCC)
记录逻辑日志:
- 当数据库执行一条insert语句时,Undo Log会记录一条对应的delete语句;
- 当数据库执行一条delete语句时,Undo Log会记录一条对应的insert语句;
- 当数据库执行一条update语句时,Undo Log会记录一条对应相反的update语句;
2.7.3 Bin Log
记录所有MySQL数据库表结构变更以及表数据变更的二进制日志,主要用于数据库的主从复制和数据恢复