为什么需要锁?
当多个用户同时对数据库的并发操作时会带来数据不一致的问题
1.丢失更新
AB两个用户同时读取同一个数据并进行修改,其中一个用户修改的结果破坏了两一个用户修改的结果
2.脏读
A用户修改了数据,随后B用户又读出该数据,但A用户因为某些原因取消了对数据的修改,数据恢复原值,此时B用户得到的数据就与数据库内的数据就不一致
3.不可重复读
A用户读取数据,随后B用户读取该数据并修改,此时A用户再读取数据时发现前后两次的值不一致
并发控制的主要方法就是封锁。锁就是在一段时间内禁止用户做某些操作来避免产生数据不一致
锁的种类:
共享锁(shared lock)
例子1:
T1:select * from table
T2: update table set column1 = "hello"
过程:
T1 运行 (加共享锁)
T2 运行
if T1没有执行完
T2等
else
共享锁释放
T2执行
T2之所以要等,是因为T2在执行update前,试图对table加一个排他锁。数据库规定同一资源上不能同时存在共享锁和排他锁。
所以T2必须等T1执行完,释放共享锁,才能加上排他锁,然后执行update语句
例子2:
T1:select * from table
T2:select * from table
这里T2不用等T1执行完,而是可以马上执行
分析:
T1运行,则table被加锁,比如叫lockA
T2运行,则再对table加一个共享锁,比如叫lockB
两个锁是可以同时存在于同一资源上的。这种称为共享锁与共享锁兼容。
共享锁不阻止其他session同时读资源,但阻止其他session update
例子3:
T1:select * from table
T2:select * from table
T3:update table set column1 = "hello"
T2不用等T1运行完就能运行,T3要等T1和T2都运行完才能运行
T3必须等T1和T2的供销社全部释放才能进行加排他锁然后执行update操作
例子4:
T1:
begin tran
select * from table(holdlock) (holdlock意思是加共享锁,直到事务结束才释放)
update table set column1 = "hello"
T2:
begin tran
select * from table(holdlock) (holdlock意思是加共享锁,直到事务结束才释放)
update table set column1 = "world"
假设T1和T2同时到达select, T1对table加共享锁,T2也对table加共享锁,当T1的select执行完,
准备执行update时,根据锁机制,T1的共享锁需要升级到排他锁才能执行接下来的update.
必须要等table的其他共享锁释放,但因为holdlock这样的共享锁只有等到事务结束后才释放,
所以因为T2的共享锁不释放而导致T1等,同理。也因为T1的共享锁不释放而导致T2等。这种情况就导致死锁。
例子5:
T1:
begin tran
update table set column1 = "hello" where id = 1
T2:
begin tran
update table set column1 = "hello1" where id = 2
看情况:
如果id是主键上面有索引,那么T1会一下子找到该条记录(id=1的记录),然后对该条记录加排他锁,
同理T2会一下子找到该条记录(id=2的记录)然后对该条记录加排他锁,T1和T2各更新各的,互不影响,T2也不要等
如果id是普通的一列,没有索引。那么当T1对id=1这一行加排他锁后,T2为了找到id=2,需要对全表扫描,那么就会预先对表加上共享锁或更新锁或排他锁
但因为T1对id=1的一条记录加了排他锁,导致T2的全表扫描进行不下去,就导致T2等待
死锁怎么解决
例子6:
T1:
begin tran
select * from table (xlock)(xlock意思是直接对表加排他锁)
update table set column1 = "hello"
T2:
begin tran
select * from table (xlock)(xlock意思是直接对表加排他锁)
update table set column1 = "hello12"
这样,当T1的select执行时,直接对表加上排他锁,T2在执行select时,就需要等T1事务完全执行完才能执行。排除了死锁的发生
当第三个用户过来想执行一个查询语句时,也因为排他锁的存在不得不等待
所以引进更新锁
更新锁(update lock)
为了解决死锁,引进更新锁
例子7:
T1:
begin tran
select * from table (updlock)(updlock意思是加更新锁)
update table set column1 = "hello"
T2:
begin tran
select * from table (updlock)(updlock意思是加更新锁)
update table set column1 = "hello12"
更新锁的意思是:“我现在只想读,你们也可以读,但我将来可能会做更新操作,我已经获取了从共享锁升级到排他锁的资格”
一个事务只能有一个更新锁获取此资格
T1执行select,加更新锁。
T2运行,准备加更新锁,但发现已经有一个更新锁在,只好等。
当后来有用户3,用户4,需要查询table表中的数据时,并不会因为T1的select在执行就被阻塞,照样能查询,和例子6相比,效率提高很多
例子8:
T1:
select * from table (updlock)(updlock意思是加更新锁)
T2:
select * from table (updlock)(updlock意思是加更新锁,等待,直到T1释放更新锁,因为同一时间不能在同一资源上有两个更新锁)
T3:
select * from table(加共享锁,但不用等updlock释放,就可以读)
这个例子说明:共享锁和更新锁可以同时在同一资源上的,称为共享锁和更新锁是兼容的
例子9:
T1:
begin tran
select * from table (updlock)(加更新锁)
update table set column1 = "hello"(这里T1做更新时,不需要等T2释放什么,而是直接把更新锁升级为排他锁,然后执行update)
T2:
begin tran
select * from table
update table set column1 = "hello12"
说明:排他锁和更新锁是不兼容的
排他锁(exclusive lock)
其他事务既不能读,又不能改排他锁锁定的资源
例子10:
T1:update table set column1 = "hello2" where id < 1000
T2:update table set column1 = "hello453" where id > 1000
假设T1先到达,T2随后到达。
这个过程T1会对id < 1000的记录加排他锁,但不会阻塞T2的update
T1:update table set column1 = "hello2" where id < 1000
T2:update table set column1 = "hello453" where id > 900
T1先到达,T2随后,T1加的排他锁会阻塞T2的update
意向锁(intent locks)
意向锁就是说在屋(比如代表一个表)门口设置一个标识,说明屋子有人(比如代表某些记录)被锁住了。另一个人想知道屋子里是否有人被锁,
不用进屋子一个一个去查,直接看门口标识就行了
当一个表中的某一行被加上排他锁后,该表就不能再被加表锁,数据库程序如何知道该表不能被加表锁?一种方式是逐条的判断该表的每一条记录是否已经有排他锁,
另一种方式是直接在表这一层检查是否有意向锁,不需要逐条判断,效率高
例子11:
T1:
begin tran
select * from table(xlock) where id = 10 对id=10这一行强加排他锁
T2:
begin tran
select * from table (tablock) 加表级锁
假设T1先执行,T2后执行,T2执行时,想要加表锁,为判断是否可以加表锁,数据库系统要逐条判断table每行记录是否已有排他锁,如果发现其中一行已经有排他锁,就不允许加表锁了。
实际上,数据库系统当T1的select执行时,系统对表的id=10的这一行加上排他锁,同时对整个表加了意向排他锁,当T2执行表锁时,只需要看这个表是否有意向排他锁存在,有就直接等待
不需要逐条检查资源了
例子12:
T1:
begin tran
update table set column1 = "Hello1" where id = 10
T2:
begin tran
update table set column1 = "Hello2" where id = 10
T1执行,系统对table同时对行加排他锁,对页加意向排他锁,对表加意向排他锁