---------------1-----------------
alter procedure AddLansare
@Nume varchar(100),
@data varchar(100),
@titluPiesa varchar(100)
as
begin
    begin tran
    begin try
        declare @idArtist int
        declare @idMelodie int

        if(dbo.validare_nume(@nume)<>1)
        begin
            print('invalid name')
            RAISERROR('invalid name',14,1)
        end
        insert into Artisti values(@Nume,3)
        set @idArtist=SCOPE_IDENTITY()
        print('insert artist')


        if(dbo.validare_nume(@titluPiesa)<>1)
        begin
            print('invalid title')
            RAISERROR('invalid title',14,1)
        end
        insert into Melodii values(@titluPiesa,2)
        set @idMelodie=SCOPE_IDENTITY()
        print('insert melodie')

        if(dbo.validare_DataCalendaristica(@data)<>1)
        begin
            print('invalid client data')
            RAISERROR('invalid client data',14,1)
        end
        print('lansare valida')
        insert into Lansare values(@idArtist,@idMelodie,@data)
        print('insert lansare')

        commit tran
        print('ok')
        select 'Transaction commited'

    end try
    begin catch
        rollback tran
        select 'Transaction rolled back'
    end catch
end

select * from Artisti
select * from Melodii
select * from Lansare
exec AddLansare 'George','04/04/2019','Ambigu'

select * from Artisti
select * from Melodii
select * from Lansare
exec AddLansare 'George','--','Ambigu'
select * from Artisti
select * from Melodii
select * from Lansare
------------------2---------------
alter procedure AddLansare2
@Nume varchar(100),
@data varchar(100),
@titluPiesa varchar(100)
as
begin
    declare @ok int
    set @ok=1
    declare @idArtist int
    declare @idMelodie int

    begin tran
    begin try
        if(dbo.validare_nume(@nume)<>1)
        begin
            print('invalid artist name')
            RAISERROR('invalid client data',14,1)
        end

        insert into Artisti values(@Nume,3)
        commit tran
        set @idArtist=SCOPE_IDENTITY()
        print('insert artist')
        end try
        begin catch
            set @ok=0
            rollback tran
            select 'Transaction rolled back at client'
        end catch

        begin tran
        begin try
        if(dbo.validare_nume(@titluPiesa)<>1)
        begin
        print('invalid song title')
            RAISERROR('invalid item data',14,1)
        end


        insert into Melodii values(@titluPiesa,3)
        set @idMelodie=SCOPE_IDENTITY()
        commit tran
        print('insert song')

        end try
        begin catch
            set @ok =0
            rollback tran
            select 'Transaction rolled back at song'
        end catch

        begin tran
        begin try
        if(dbo.validare_DataCalendaristica(@data)<>1)
        begin
            print('invalid launch data')
            RAISERROR('invalid launch data',14,1)
        end
        print('lansare valida')
        insert into Lansare values(@idArtist,@idMelodie,@data)
        print('insert lansare')

        if @ok <> 1
        begin
            return
        end

        commit tran
        print('ok')
        select 'Transaction commited'
        end try
        begin catch
            rollback tran
            select 'Transaction rolled back at lansare'
        end catch
end

select * from Artisti
select * from Melodii
select * from Lansare
exec AddLansare2 'Alin','04/04/2019','Piesa1'

select * from Artisti
select * from Melodii
select * from Lansare
exec AddLansare2 'Alin','--','Piesa1'
select * from Artisti
select * from Melodii
select * from Lansare

delete from Artisti where ArtistID=21
delete from Melodii where MelodieID=19
delete from Lansare where ArtistID=21

-----------------3------------
---Dirty Reads
BEGIN TRANSACTION
UPDATE Impresar set Nume='Alexandru'
WHERE ImpresarID=5
WAITFOR DELAY '00:00:10'
ROLLBACK TRANSACTION

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

--sol:set transaction isolation level to read committed

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT * FROM Impresar
WAITFOR DELAY '00:00:15'
SELECT * FROM Impresar
COMMIT TRAN

--Non repeatable
INSERT INTO Impresar values('Eusebiu')
BEGIN TRAN
WAITFOR DELAY '00:00:05'
UPDATE Impresar SET Nume='aaaa' where
Nume = 'Eusebiu'
COMMIT TRAN

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

---non repeatable sol
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM Impresar
WAITFOR DELAY '00:00:05'
SELECT * FROM Impresar
COMMIT TRAN

--Phantom Reads
BEGIN TRAN
WAITFOR DELAY '00:00:04'
INSERT INTO Impresar VALUES('Bogdan')
COMMIT TRAN

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

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

---DEADLOCK

--transaction 1
begin tran
update Locatie set Tara='Dead'
waitfor delay '00:00:10'
update Concerte set DataConcert='04/04/2012'
commit tran

--transaction 2
begin tran
update Concerte set DataConcert='04/04/2012'
waitfor delay '00:00:10'
update Locatie set Tara='Dead'
commit tran

--DEADLOCK SOLUTION
SET DEADLOCK_PRIORITY HIGH
begin tran
update Locatie set Tara='Dead'
waitfor delay '00:00:10'
update Concerte set DataConcert='04/04/2012'
commit tran



create procedure Dead1 as
begin
    begin tran
    update Concerte set DataConcert='04/04/2012'
    waitfor delay '00:00:10'
    update Locatie set Tara='Dead'
    commit tran
end

create procedure Dead2 as
begin
    SET DEADLOCK_PRIORITY HIGH
    begin tran
    update Locatie set Tara='Dead'
    waitfor delay '00:00:10'
    update Concerte set DataConcert='04/04/2012'
    commit tran
end

exec Dead1
exec Dead2
---------------1-----------------
alter procedure AddLansare
@Nume varchar(100),
@data varchar(100),
@titluPiesa varchar(100)
as
begin
    begin tran
    begin try
        declare @idArtist int
        declare @idMelodie int

        if(dbo.validare_nume(@nume)<>1)
        begin
            print('invalid name')
            RAISERROR('invalid name',14,1)
        end
        insert into Artisti values(@Nume,3)
        set @idArtist=SCOPE_IDENTITY()
        print('insert artist')


        if(dbo.validare_nume(@titluPiesa)<>1)
        begin
            print('invalid title')
            RAISERROR('invalid title',14,1)
        end
        insert into Melodii values(@titluPiesa,2)
        set @idMelodie=SCOPE_IDENTITY()
        print('insert melodie')

        if(dbo.validare_DataCalendaristica(@data)<>1)
        begin
            print('invalid client data')
            RAISERROR('invalid client data',14,1)
        end
        print('lansare valida')
        insert into Lansare values(@idArtist,@idMelodie,@data)
        print('insert lansare')

        commit tran
        print('ok')
        select 'Transaction commited'

    end try
    begin catch
        rollback tran
        select 'Transaction rolled back'
    end catch
end

select * from Artisti
select * from Melodii
select * from Lansare
exec AddLansare 'George','04/04/2019','Ambigu'

select * from Artisti
select * from Melodii
select * from Lansare
exec AddLansare 'George','--','Ambigu'
select * from Artisti
select * from Melodii
select * from Lansare
------------------2---------------
alter procedure AddLansare2
@Nume varchar(100),
@data varchar(100),
@titluPiesa varchar(100)
as
begin
    declare @ok int
    set @ok=1
    declare @idArtist int
    declare @idMelodie int

    begin tran
    begin try
        if(dbo.validare_nume(@nume)<>1)
        begin
            print('invalid artist name')
            RAISERROR('invalid client data',14,1)
        end

        insert into Artisti values(@Nume,3)
        commit tran
        set @idArtist=SCOPE_IDENTITY()
        print('insert artist')
        end try
        begin catch
            set @ok=0
            rollback tran
            select 'Transaction rolled back at client'
        end catch

        begin tran
        begin try
        if(dbo.validare_nume(@titluPiesa)<>1)
        begin
        print('invalid song title')
            RAISERROR('invalid item data',14,1)
        end


        insert into Melodii values(@titluPiesa,3)
        set @idMelodie=SCOPE_IDENTITY()
        commit tran
        print('insert song')

        end try
        begin catch
            set @ok =0
            rollback tran
            select 'Transaction rolled back at song'
        end catch

        begin tran
        begin try
        if(dbo.validare_DataCalendaristica(@data)<>1)
        begin
            print('invalid launch data')
            RAISERROR('invalid launch data',14,1)
        end
        print('lansare valida')
        insert into Lansare values(@idArtist,@idMelodie,@data)
        print('insert lansare')

        if @ok <> 1
        begin
            return
        end

        commit tran
        print('ok')
        select 'Transaction commited'
        end try
        begin catch
            rollback tran
            select 'Transaction rolled back at lansare'
        end catch
end

select * from Artisti
select * from Melodii
select * from Lansare
exec AddLansare2 'Alin','04/04/2019','Piesa1'

select * from Artisti
select * from Melodii
select * from Lansare
exec AddLansare2 'Alin','--','Piesa1'
select * from Artisti
select * from Melodii
select * from Lansare

delete from Artisti where ArtistID=21
delete from Melodii where MelodieID=19
delete from Lansare where ArtistID=21

-----------------3------------
---Dirty Reads
BEGIN TRANSACTION
UPDATE Impresar set Nume='Alexandru'
WHERE ImpresarID=5
WAITFOR DELAY '00:00:10'
ROLLBACK TRANSACTION

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

--sol:set transaction isolation level to read committed

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT * FROM Impresar
WAITFOR DELAY '00:00:15'
SELECT * FROM Impresar
COMMIT TRAN

--Non repeatable
INSERT INTO Impresar values('Eusebiu')
BEGIN TRAN
WAITFOR DELAY '00:00:05'
UPDATE Impresar SET Nume='aaaa' where
Nume = 'Eusebiu'
COMMIT TRAN

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

---non repeatable sol
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM Impresar
WAITFOR DELAY '00:00:05'
SELECT * FROM Impresar
COMMIT TRAN

--Phantom Reads
BEGIN TRAN
WAITFOR DELAY '00:00:04'
INSERT INTO Impresar VALUES('Bogdan')
COMMIT TRAN

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

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

---DEADLOCK

--transaction 1
begin tran
update Locatie set Tara='Dead'
waitfor delay '00:00:10'
update Concerte set DataConcert='04/04/2012'
commit tran

--transaction 2
begin tran
update Concerte set DataConcert='04/04/2012'
waitfor delay '00:00:10'
update Locatie set Tara='Dead'
commit tran

--DEADLOCK SOLUTION
SET DEADLOCK_PRIORITY HIGH
begin tran
update Locatie set Tara='Dead'
waitfor delay '00:00:10'
update Concerte set DataConcert='04/04/2012'
commit tran



create procedure Dead1 as
begin
    begin tran
    update Concerte set DataConcert='04/04/2012'
    waitfor delay '00:00:10'
    update Locatie set Tara='Dead'
    commit tran
end

create procedure Dead2 as
begin
    SET DEADLOCK_PRIORITY HIGH
    begin tran
    update Locatie set Tara='Dead'
    waitfor delay '00:00:10'
    update Concerte set DataConcert='04/04/2012'
    commit tran
end

exec Dead1
exec Dead2