Scribd
Upload
4 views7 pages

Database Concurrency Issues Explained

The document discusses concurrency problems in databases, specifically focusing on dirty reads, non-repeatable reads, phantom reads, and deadlocks, using SQL examples. It illustrates how different transaction isolation levels can resolve these issues and provides solutions for managing deadlocks through transaction priorities. The document includes SQL commands for creating tables, inserting data, and demonstrating the effects of various transaction scenarios.

Uploaded by

TeoONシ
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views7 pages

Database Concurrency Issues Explained

The document discusses concurrency problems in databases, specifically focusing on dirty reads, non-repeatable reads, phantom reads, and deadlocks, using SQL examples. It illustrates how different transaction isolation levels can resolve these issues and provides solutions for managing deadlocks through transaction priorities. The document includes SQL commands for creating tables, inserting data, and demonstrating the effects of various transaction scenarios.

Uploaded by

TeoONシ
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Concurrency problems – a simple example

We consider the database


create database DBMS_Lab3_
go
use DBMS_Lab3_
go

CREATE TABLE Books(


id INT PRIMARY KEY IDENTITY,
title varchar(100),
language varchar(100))

CREATE TABLE Authors(


id INT PRIMARY KEY IDENTITY,
name varchar(100))

CREATE TABLE BooksAuthors(


author_id INT FOREIGN KEY REFERENCES Authors(id),
book_id INT FOREIGN KEY REFERENCES Books(id),
CONSTRAINT pk_BooksAuthors PRIMARY KEY (author_id, book_id))

In table Books we have


-- add records
insert into Books values
('Pride and Prejudice', 'english'),
('Harry Potter and the Chamber of Secrets', 'english'),
('Panda', 'english')

select * from Books

Please, put in each file use DBMS_Lab3_

In what follows, we will work with the table Books and


T1=Transaction 1 starts first and finish first (the first column form the table(s))
T2=Transaction start immediately after T1 and finish after T1 (the second column form the table(s))

1. DIRTY READS – T1: update + delay + rollback, T2: select + delay + select -> see the update in the first
select, even if it is rollback then (the order in the execution of the operations is: update – select –
rollback – select)
Isolation level: Read Uncommitted / Read Committed (solution)
-- dirty reads
select * from Books

BEGIN TRANSACTION --PROBLEM: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


UPDATE Books SET SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
language='Romanian' WHERE id = 2 BEGIN TRAN
WAITFOR DELAY '00:00:10' SELECT * FROM Books
ROLLBACK TRANSACTION WAITFOR DELAY '00:00:15'
SELECT * FROM Books
COMMIT TRAN

1
(1 row(s) affected)

-- dirty reads
select * from Books

BEGIN TRANSACTION -- SOLUTION: SET TRANSACTION ISOLATION LEVEL TO READ COMMITTED


UPDATE Books SET SET TRANSACTION ISOLATION LEVEL READ COMMITTED
language='Romanian' WHERE id = 2 BEGIN TRAN
WAITFOR DELAY '00:00:10' SELECT * FROM Books
ROLLBACK TRANSACTION WAITFOR DELAY '00:00:15'
SELECT * FROM Books
COMMIT TRAN
(1 row(s) affected)

-- dirty reads
select * from Books

2. NON-REPEATABLE READS – T1: delay + update + commit, T2: select + delay + select -> see the value
inserted before the transaction from the first select of T2 + see the update of the value inserted before the
transaction, from the second select of T2 (the order in the execution of the operations is: select – update – select)
Isolation level: Read Committed / Repeatable Read (solution)
-- nonrepeatable reads
select * from Books

INSERT INTO Books(title, language) VALUES ('Peter --PROBLEM: SET TRANSACTION ISOLATION LEVEL READ
Pan','Romanian') UNCOMMITTED
BEGIN TRAN SET TRANSACTION ISOLATION LEVEL READ COMMITTED
WAITFOR DELAY '00:00:10' BEGIN TRAN
UPDATE Books SET language='English' WHERE title = SELECT * FROM Books
'Peter Pan' WAITFOR DELAY '00:00:15'
COMMIT TRAN SELECT * FROM Books
COMMIT TRAN

2
(1 row(s) affected) –- from insert

(1 row(s) affected) – from update

-- nonrepeatable reads
select * from Books
delete from Books where title = 'Peter Pan'
select * from Books

INSERT INTO Books(title, language) VALUES ('Peter -- SOLUTION: SET TRANSACTION ISOLATION LEVEL TO
Pan','Romanian') REPEATABLE READ
BEGIN TRAN SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
WAITFOR DELAY '00:00:10' BEGIN TRAN
UPDATE Books SET language='English' WHERE title = SELECT * FROM Books
'Peter Pan' WAITFOR DELAY '00:00:15'
COMMIT TRAN SELECT * FROM Books
COMMIT TRAN

(1 row(s) affected)

(1 row(s) affected)

-- nonrepeatable reads
select * from Books

3. PHANTOM READS – T1: delay + insert + commit, T2: select + delay + select -> see the inserted value
only at the second select from T2 (the order in the execution of the operations is: select – insert – select)
Isolation level: Repeatable Read / Serializable (solution)

3
-- phantom reads
select * from Books

BEGIN TRAN -- PROBLEM: SET TRANSACTION ISOLATION LEVEL TO


WAITFOR DELAY '00:00:10' REPEATABLE READ
INSERT INTO Books(title,language) VALUES ('At the SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
end of the world','English') BEGIN TRAN
COMMIT TRAN SELECT * FROM Books
WAITFOR DELAY '00:00:15'
SELECT * FROM Books
COMMIT TRAN
(1 row(s) affected)

-- phantom reads
select * from Books
delete from Books where title='At the end of the
world'
select * from Books

BEGIN TRAN -- SOLUTION: SET TRANSACTION ISOLATION LEVEL TO


WAITFOR DELAY '00:00:10' SERIALIZABLE
INSERT INTO Books(title,language) VALUES ('At the SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
end of the world','English') BEGIN TRAN
COMMIT TRAN SELECT * FROM Books
WAITFOR DELAY '00:00:15'
SELECT * FROM Books
COMMIT TRAN
(1 row(s) affected)

4
-- phantom reads
select * from Books

DEADLOCK - T1: update on table A + delay + update on table B


- T2: update on table B + delay + update on table A
T1- update on table A -> delay Try to update table Table B is blocked in One of the blocked
exclusive lock on table A B T2 transactions, T1 or
T2, will be chosen
T2 - update on table B -> delay as a deadlock victim
exclusive lock on table B Try to update table Table A is blocked in and terminates with
A T1 an error. The other
transaction wins and
update both table A
and table B

The only solution is to decide which of the 2 transactions to win, by using the DEADLOCK_PRIORITY, that
can be set (LOW, NORMAL, HIGH, or from -10 (-5) to 10 (5)). Implicit is NORMAL (0).

The victim transaction is chosen like this:


1. The transaction with the lowest DEADLOCK_PRIORITY
2. If both of the transactions have the same DEADLOCK_PRIORITY, the victim is the one, less
expensive at ROLLBACK
3. If both of the transactions have the same DEADLOCK_PRIORITY and the same cost, the victim is
chosen randomly

We consider tables: Books and Authors


-- deadlock
insert into Books values ('Book for Deadlock', 'english')
insert into Authors values ('Author for Deadlock')
select * from Books
select * from Authors

Deadlock example:
-- transaction 1
begin tran (1 row(s) affected)
update Books set title='deadlock Books Transaction 1' where id=8
-- this transaction has exclusively lock on table Books (1 row(s) affected)
waitfor delay '00:00:10'
update Authors set name='deadlock Authors Transaction 1' where id=1
commit tran
-- transaction 2
begin tran
5
update Authors set name='deadlock Authors Transaction 2' where id=1
-- this transaction has exclusively lock on table Authors
waitfor delay '00:00:10'
update Books set title='deadlock Books Transaction 2' where id=8
commit tran

select * from Books


select * from Authors

So, transaction 1 is the winner and update the


tables Books and Authors, and transaction 2 is the
victim

If in transaction 2, we set DEADLOCK_PRIORITY to HIGH, or, if in transaction 1 we set


DEADLOCK_PRIORITY to LOW, the winner will be transaction 2 and the victim transaction 1.
-- transaction 1
begin tran
update Books set title='deadlock Books Transaction 1' where id=8
-- this transaction has exclusively lock on table Books
waitfor delay '00:00:10'
update Authors set name='deadlock Authors Transaction 1' where id=1
commit tran

-- transaction 2 (1 row(s) affected)


SET DEADLOCK_PRIORITY HIGH
-- SET DEADLOCK_PRIORITY LOW (1 row(s) affected)
begin tran
update Authors set name='deadlock Authors Transaction 2' where id=1
-- this transaction has exclusively lock on table Authors
waitfor delay '00:00:10'
update Books set title='deadlock Books Transaction 2' where id=8
commit tran
select * from Books
select * from Authors

Another possible solution for Deadlock is to execute the statements in the same order in both of the
transactions. As result, first are performed the UPDATE’s from the first transaction executed and then the
UPDATE’s from the second transaction executed.
6
-- transaction 1
begin tran
update Books set title='deadlock Books Transaction 1' where id=1047
-- this transaction has exclusively lock on table Books
waitfor delay '00:00:10'

update Authors set name='deadlock Authors Transaction 1' where id=1006


commit tran

-- (1 row affected)
-- (1 row affected)
-- transaction 2
begin tran
update Books set title='deadlock Books Transaction 2' where id=1047
-- this transaction has exclusively lock on table Books
waitfor delay '00:00:10'

update Authors set name='deadlock Authors Transaction 2' where id=1006


commit tran

-- (1 row affected)
-- (1 row affected)
select * from Books
select * from Authors

You might also like