数据库与锁机制
数据库事务的ACID四大特性:
原子性(Atomicity)
事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
一致性(Consistency)
在事务开始和完成时,数据都必须保持一致状态。比如用户下单,订单、订单商品、用户扣款数据必须同时成功,要么就全部失败,保证数据从一个一致状态过渡到另一个一致状态。
隔离性(Isolation)
数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
持久性(Durability)
事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
数据库并发事务存在问题:
脏读: 一个事务可以读取到其他事务未提交的内容。
不可重复读: 在一个事务范围内,先后两次读取了同一条记录,却获得不同的结果。这是因为在第一次读取后,有其他事务修改了这条记录并提交到了数据库,再次读取后的记录是被修改后的数据。
幻读: 在一个事务范围内,先后两次读取同一个范围列表,却获得不同的结果集。这是因为在两次读取的过程中,有其他事务往这个范围中插入了新的数据。
丢失更新:在不加锁的情况下,一个事务内先读取数据,做业务处理之后再更新该记录。在多线程并发的时候,将会造成丢失更新的问题。这是因为一个事务读取了数据,在做业务处理的过程中,有其他事务更新了数据并提交到了数据库,当前事务再更新的时候,就会把之前的更新覆盖掉,导致丢失更新的问题。打个比方,小郭去A窗口买2张高铁票,售票员先查询余票,发现还有10张,就给小明办理买票手续。此时小明在B窗口也买了1张同一班的高铁票并取票离开了,B窗口的售票员将余票更新成了9张。A窗口售票员给小郭办好了出票手续,将之前查询出来的10张高铁票减去两张,并更新数据库中的余票数量为8张。结果就是明明卖了三张高铁票,余票却只减少了两张!
隔离级别:
读未提交(read uncommitted)
一个事务可以读取到其他事务未提交的内容。
该级别并发度最高,但完全不能避免脏读、不可重复读、幻读
读已提交(read committed)
一个事务可以读取其他事务已提交的内容。
避免了脏读,但不能避免不可重复读和幻读
该级别为多数数据库的默认隔离级别,如: oracle
可重复读(repeatable read)
一个事务中反复读取同一条记录得到是完全相同的结果
避免了脏读、不可重复读,正常情况下不能避免幻读(mysql除外)
mysql innoDB的默认隔离级别为可重复读,可以避免幻读
串行化(serializable)
Serializable 是最高的事务隔离级别,在该级别下,事务串行化顺序执行,可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率非常低下,消耗数据库性能,一般不使用。
数据库锁
- 表级锁:每次操作锁住整张表。开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低;
- 行级锁:每次操作锁住一行数据。开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高;
- 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
mysql锁详解:看这里
下面以mysql innodb为例探究数据库事务与所机制
下表作为测试,order_num为主键,item_id为索引
设置mysql 默认可重复读隔离级别:
set session transaction isolation level repeatable read;
start transaction;
测试1(可重复读):
事务A |
事务B |
select * from order_test; |
|
|
update order_test set buy_num=10; |
select * from order_test; |
|
|
commit; |
select * from order_test; |
|
事务A三次查询结果完全一致,事务B的数据更新、提交后,A查询出来的还是之前的数据,解决了读未提交和不可重复读的问题。 |
测试2(不完全可靠的可重复读):
事务A |
事务B |
select buy_num from order_test where order_num=105; 返回橙子购买数量为15 |
|
|
update order_test set buy_num=10 where order_num=105; commit; 更新橙子购买数量为10,并提交 |
Update order_test set buy_num=buy_num+1 where order_num=105; 橙子的购买数量加1 |
|
select buy_num from order_test where order_num=105; 返回橙子购买数量为11!!!对于A事务来说,相当于15+1=11!! |
|
由于普通查询语句未加任何锁,事务A未完成时,其他事务仍可对其所查询的语句进行修改操作,mysql实现的可重复读并不绝对可靠。 |
测试3(丢失更新):
事务A |
事务B |
select buy_num from order_test where order_num=105; 返回橙子购买数量为15 |
|
|
update order_test set buy_num= 16 where order_num=105; commit; 将橙子购买数量+1,并提交,此时数据库中橙子数量为16 |
Update order_test set buy_num=16 where order_num=105; 根据第一步查询出来的橙子数量,业务代码中加1之后,再更新至数据库 |
|
两个独立的事务分别对橙子数量+1之后,数据库中的橙子数量只是从15增加到了16!造成了丢失更新的问题。 |
针对测试2、3的问题,可在第一条查询语句后面加上lock in share mode或者 for update
X(写锁) |
S(共享锁) |
|
X |
冲突 |
冲突 |
S |
冲突 |
兼容 |
其中lock in share mode将给数据添加共享锁,容易造成死锁,不推荐用于查询出来之后需要在事务内进行更新的场合;for update将给数据添加写锁,推荐使用。下面具体看看为什么添加共享锁容易造成死锁。
测试4(死锁测试):
事务A |
事务B |
select buy_num from order_test where order_num=105 lock in share mode; 加共享锁 |
|
|
Update order_test set buy_num=1 where order_num=105;加共享锁 |
Update order_test set buy_num= 1 where order_num=105; 由于事务B对该条记录加了共享锁,所以只能等待事务B提交 |
|
|
Update order_test set buy_num=1 where order_num=105; 由于事务A对该条记录也加了共享锁,所以只能等待事务A提交 |
两个事务都不能往下执行,互相等待对方释放锁,造成死锁。 如果将lock in share mode 换成 for update(写锁),则不会出现这个问题。 在查询语句后加for update,适用于先查询数据,再根据查询的结果,做业务处理计算出新值后,直接更新前面数据的场合,可以有效防止丢失更新问题,很重要 |
测试5(间隙锁):
事务A |
事务B |
select * from order_test where order_num >105 and order_num <124 for update; |
|
|
update order_test set buy_num=10 where order_num=111; 数据被锁住了,无法更新 |
|
insert into order_test values(107,309,’茄子’,8,now()); 等待执行,范围内的值也无法插入 |
|
update order_test set buy_num=10 where order_num=105; 更新成功 |
|
insert into order_test values(90,309,’茄子’,8,now()); 插入成功,范围外的写数据不受影响 |
InnoDB中的行锁+间隙锁Next-Key Lock。A事务范围更新语句将给范围内的数据行添加行锁,其他事务只能读不能写;范围间的间隙添加间隙锁,该示例中将在(105,111),(111,123),(123,124)之间添加间隙锁,间隙中不能插入新的记录, 该机制使得mysql在可重复读级别(repeatable read)解决了幻读的问题 |
测试6(mysql的表锁):
事务A |
事务B |
update order_test set buy_num=25 where item_name =’西瓜’; (无主键、索引,table lock) |
|
|
update order_test set buy_num=10 where order_num=105; 表被锁住,无法更新 |
update order_test set buy_num=25 where order_num =90; (指定主键,若查无数据,加间隙锁(-∞,90)(90,105)) |
|
|
Insert into order_test values(95,100,’哈密瓜’,9,now()); 间隙内,无法更新 |
|
insert into order_test values(89,309,’茄子’,8,now()); 插入成功,间隙外不受影响 |
update order_test set buy_num =23 where order_num like ‘11%’;(主键不明确,table lock) |
|
|
update order_test set buy_num=10 where order_num=105; 表被锁住,无法更新 |
InnoDB 预设是Row-Level Lock,所以只有明确的指定主键或索引,MySQL 才会执行Row lock (只锁住被选取的数据) ,否则MySQL 将会执行Table Lock (将整个数据表单给锁住) |