2PL锁是很古老的数据库并发控制,即读写锁(也叫共享锁和排他锁):read_lock(X),write_lock(X)
2PL协议将事务分为两部分:
-
扩展阶段(获取锁,并且不允许释放锁)
-
收缩阶段(释放所有锁,并且无法进一步获取其他锁)
2PL中进行多模式锁的规则
1.read(X)前得有read_lock(X)锁
2.同理write(X)前得有write_lock(X)锁
3.事务结束后要释放资源unlock(X)
4.一个事务对资源read_lock(X)后 别的事务可以继续对该资源上读锁。
5.一个事务对资源write_lock(X)后 别的事务不能再对该资源上读写锁,进入等待状态
6.在read_lock(X)后再加 write_lock(X),进行了锁强化
7.事务unlock(X)资源只有在该资源共享了写锁或独占了读锁的时候可以释放。
上述规则的原因可以从简易的算法中看出
READ_LOCK(X)的算法
B:if LOCK(X) = UNLOCKED
then LOCK(X):= READ_LOCKED //资源未上读锁
No_Of_Reads(X) := 1
else if LOCK(X) = READ_LOCKED then
No_Of_Reads(X) := No_Of_Reads(X) + 1 //计数共享锁的数量
else
do wait (until LOCK(X)!=WRITE_LOCKED and the lock //加强锁
manager wakes up the transaction)
end wait
goto B
end if
write_lock(X)的算法
C: if LOCK(X) = UNLOCKED then
LOCK(X):= WRITE_LOCKED
else
do wait (until LOCK(X)=UNLOCKED and the
lock manager wakes up the transaction)
end wait
goto C
end if
unlock(X)的算法
if LOCK(X) = WRITE_LOCKED then
LOCK(X) := UNLOCKED
if any transactions are waiting
then wake up one or more of the transactions
end if
else if LOCK(X) = READ_LOCKED then
No_Of_Reads(X) := No_Of_Reads(X) – 1 //共享锁数量为0时释放共享锁
if (No_Of_Reads(X) = 0) then
LOCK(X) := UNLOCKED
if any transactions are waiting
then wake up one or more of the transactions
end if
end if
end if
在调度表上加2PL锁来检查事务的运行
对表S1 对表S2
S1 | ||
---|---|---|
T1 | T2 | 注释 |
Read_lock(a) | ||
R(a) | ||
a:=a-10 | ||
Write_lock(a) | 资源X的读锁只有一个,所以可加加强锁,write_lock | |
W(a) | ||
Read_lock(b) | ||
R(b) | ||
b:=b+10 | ||
Write_lock(b) | ||
W(b) | ||
Unlock(a,b) | T1运行完,释放资源 | |
Read_lock(b) | ||
R(b) | ||
b:=b-20 | ||
Write_lock(b) | 同理只有一个写锁可以加write_lock | |
W(b) | ||
Read_lock(c) | ||
R(c) | ||
c:=c+20 | ||
Write_lock(c) | ||
W(c) | ||
Unlock(b,c) |
S2 | ||
T1 | T2 | 注释 |
Read_lock(a) | ||
R(a) | ||
Read_lock(b) | ||
R(b) | ||
a:=a-10 | ||
b:=b-20 | ||
Write_lock(a) | 加强锁对a | |
W(a) | ||
Write_lock(b) | 加强锁对b | |
W(b) | ||
Read_lock(b) | 此时b上的共享锁数量为2,因为T2上并未释放加强锁write_lock,此时的read_lock(b)进入等待 | |
Read_lock(c) | ||
R(c) | ||
c:=c+20 | ||
Write_lock(c) | 加强锁对b | |
W(c) | ||
Unlock(b,c) | ||
R(b) | T2释放bc资源,T1继续运行 | |
b:=b+10 | ||
Write_lock(b) | 加强锁对b | |
W(b) | ||
Unlock(a,b) |
也可以看出S1 S2是可序列化的。S1还是可串行的。
对表S3
S3 | ||
---|---|---|
T1 | T2 | 注释 |
Read_lock(a) | ||
R(a) | ||
a:=a-10 | ||
Read_lock(b) | ||
R(b) | ||
Write_lock(a) | 加强锁对a | |
W(a) | ||
b:=b-20 | ||
Read_lock(b) | 对b的共享锁数量此时有两个 | |
R(b) | ||
Write_lock(b) | 因为对b的共享锁有两个,不能加强该锁,此时进入等待 | |
b:=b+10 | ||
Write_lock(b) | 同理,对b共享锁有两个,进入等待 | |
.. | ||
DEADLOCK | DEADLOCK | 互相等待,死锁。 |