--Dirty Reads Part 1
BEGIN TRANSACTION
UPDATE Adresa SET strada = 'DENUMIRE'
WHERE id_adresa = 7
WAITFOR DELAY '00:00:10'
ROLLBACK TRANSACTION

--Dirty Reads Part 2
SET TRANSACTION ISOLATION LEVEL READ
UNCOMMITTED
BEGIN TRAN
SELECT * FROM Adresa
WAITFOR DELAY '00:00:15'
SELECT * FROM Adresa
COMMIT TRAN

--Solution: SET TRANSACTION ISOLATION LEVEL TO READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT * FROM Adresa
WAITFOR DELAY '00:00:15'
SELECT * FROM Adresa
COMMIT TRAN

-- Non repeatable 1
INSERT INTO Adresa VALUES('dsda')
BEGIN TRAN
WAITFOR DELAY '00:00:05'
UPDATE Adresa SET strada='DDD' WHERE
strada = 'dsda'
COMMIT TRAN

-- Non repeatable 2
SET TRANSACTION ISOLATION LEVEL READ
COMMITTED
BEGIN TRAN
SELECT * FROM Adresa
WAITFOR DELAY '00:00:05'
SELECT * FROM Adresa
COMMIT TRAN

-- Non repeatable SOLUTION
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM Adresa
WAITFOR DELAY '00:00:05'
SELECT * FROM Adresa
COMMIT TRAN

-- Phantom Reads Part 1
-- DELETE FROM LiniiProduse
BEGIN TRAN
WAITFOR DELAY '00:00:04'
INSERT INTO Adresa VALUES('333')
COMMIT TRAN

--Phantom Reads Part 2
SET TRANSACTION ISOLATION LEVEL REPEATABLE
READ
BEGIN TRAN
SELECT * FROM Adresa
WAITFOR DELAY '00:00:05'
SELECT * FROM Adresa
COMMIT TRAN

-- Phantom reads SOLUTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM Adresa
WAITFOR DELAY '00:00:05'
SELECT * FROM Adresa
COMMIT TRAN


-- DEADLOCK

-- transaction 1
BEGIN tran
UPDATE Persoana SET varsta=5000
waitfor delay '00:00:10'
UPDATE Persoana SET nume='DEAD'
commit tran


-- transaction 2
BEGIN tran
UPDATE Persoana SET nume='DEAD'
waitfor delay '00:00:10'
UPDATE Persoana SET varsta=500
commit tran

-- deadlock SOLUTION

-- transaction 2
SET DEADLOCK_PRIORITY HIGH
BEGIN tran
UPDATE Adresa SET strada='DEAD'
waitfor delay '00:00:10'
UPDATE Persoana SET varsta=500
commit tran

EXEC dbo.Dead2