数据库与锁机制

数据库事务的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 (将整个数据表单给锁住)