Архив / DBMSlide07транзакции-блокировки
.pdf
Блокировки – II
Транзакция А |
Таблица 1 |
Таблица 2 |
Таблица 3 |
Транзакция В |
Update t1 |
X–Locked A |
|
|
|
|
|
|
X–Locked B |
Update t3 |
Select t2 |
|
S–Locked A |
|
|
|
|
S–Locked A, B |
|
Select t2 |
|
|
S–Locked A |
Unlocked |
Commit |
Update t1 |
|
|
|
|
Update t2 |
|
X–Locked A |
|
|
Commit |
Unlocked |
Unlocked |
|
|
Щеголева Л. В. (ПетрГУ) |
Базы данных |
8 / 12 |
Блокировки – II
Транзакция А |
Таблица 1 |
Таблица 2 |
Таблица 3 |
Транзакция В |
Update t1 |
X–Locked A |
|
|
|
|
|
|
X–Locked B |
Update t3 |
Select t2 |
|
S–Locked A |
|
|
|
|
S–Locked A, B |
|
Select t2 |
Update t2 |
|
wait A |
|
|
|
|
S–Locked A |
Unlocked |
Commit |
|
|
X–Locked A |
|
|
Update t1 |
|
|
|
|
Update t2 |
|
|
|
|
Commit |
Unlocked |
Unlocked |
|
|
Щеголева Л. В. (ПетрГУ) |
Базы данных |
9 / 12 |
Тупик
Транзакция А |
Таблица 1 |
Таблица 2 |
Транзакция В |
Update t1 |
X–Locked A |
|
|
|
|
X–Locked B |
Update t2 |
Update t2 |
|
wait A |
|
|
wait B |
wait A |
Update t1 |
|
wait B |
wait A |
|
|
. . . |
. . . |
|
Щеголева Л. В. (ПетрГУ) |
Базы данных |
10 / 12 |
Уровни изолированности транзакций
Read Uncommitted (Dirty Read) – ”незафиксированное” чтение Read Committed – невоспроизводимое (или неповторяемое) чтение Repeatable Read – воспроизводимое (или повторяемое) чтение Serialized – сериализуемость
Щеголева Л. В. (ПетрГУ) |
Базы данных |
11 / 12 |
Журнал транзакций
Event |
TextData |
Application |
Login |
Dura- |
|
Start |
|
End |
|
||||||
Class |
TextData |
Name |
|
Name |
tion |
|
Time |
|
Time |
||||||
17 |
– |
network protocol: |
Microsoft |
user |
NULL |
|
06.02.2012 |
|
NULL |
|
|||||
|
set |
transaction isola- |
SQL |
Server |
|
|
|
|
|
|
15:23 |
|
|
|
|
|
tion level read com- |
Management |
|
|
|
|
|
|
|
|
|
|
|
||
|
mitted |
Studio |
|
|
|
|
|
|
|
|
|
|
|
|
|
12 |
|
|
Microsoft |
user |
1000 |
|
06.02.2012 |
|
06.02. |
|
|||||
|
insert into tblSport |
SQL |
Server |
|
|
|
|
|
|
15:25 |
|
|
15:25 |
|
|
|
Management |
|
|
|
|
|
|
|
|
|
|
|
|||
|
values( 105876, 1, |
|
|
|
|
|
|
|
|
|
|
|
|||
|
Studio - Query |
|
|
|
|
|
|
|
|
|
|
|
|||
|
’Самбо’) |
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
12 |
|
|
Microsoft |
user |
1000 |
|
06.02.2012 |
|
06.02. |
|
|||||
|
select * from |
SQL |
Server |
|
|
|
|
|
|
15:27 |
|
|
15:27 |
|
|
|
Management |
|
|
|
|
|
|
|
|
|
|
|
|||
|
mydb.dbo.tblSport |
|
|
|
|
|
|
|
|
|
|
|
|||
|
Studio - Query |
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
12 |
|
|
Microsoft |
user |
2000 |
|
06.02.2012 |
|
06.02. |
|
|||||
|
delete from |
SQL |
Server |
|
|
|
|
|
|
15:28 |
|
|
15:28 |
|
|
|
Management |
|
|
|
|
|
|
|
|
|
|
|
|||
|
mydb.dbo.tblSport |
|
|
|
|
|
|
|
|
|
|
|
|||
|
Studio - Query |
|
|
|
|
|
|
|
|
|
|
|
|||
|
where intPlayer=2 |
|
|
|
|
|
|
|
|
|
|
|
|||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Щеголева Л. В. (ПетрГУ) |
Базы данных |
12 / 12 |