Transaction i MSSQL

[Manfred]

Jeg er sikkert langt ute på et jorde og sykler, men kan noen forklare hvorfor dette skjer og hva jeg kan gjøre annerledes?

 

CREATE PROCEDURE dbo.Update_Msisdn
@Msisdn Numeric,
@Mcc Smallint,
@Mnc Smallint,
@Spid Smallint=NULL,
@CpaBarred Bit=0,
@Turnover Smallint=0,
@ErrorCode Smallint=NULL
AS
Set Nocount On

Begin Transaction

If Exists(SELECT TOP 1 1 FROM Msisdn WHERE Msisdn=@Msisdn) Begin
UPDATE Msisdn SET mnc=@mnc, MonthlyTurnover=MonthlyTurnover+@Turnover, CpaBarred=@CpaBarred, ErrorCode=@ErrorCode, LastUpdate=getDate() WHERE Msisdn=@Msisdn
End 
Else Begin
INSERT INTO Msisdn (Msisdn, Mcc, Mnc, MonthlyTurnover, CpaBarred, Errorcode) VALUES (@Msisdn, @Mcc, @Mnc, @Turnover, @CpaBarred, @Errorcode)
End 

If @@error <> 0 Rollback Transaction 
Else Commit Transaction

SELECT @Msisdn

 

Feltet Msisdn er pk i Msisdn-tabellen, og jeg får feilmeldingen "Violation of PRIMARY KEY constraint 'PK_Msisdn'. Cannot insert duplicate key in object 'dbo.Msisdn'.". Men burde ikke transactionen bare rollbacke og ikke gi meg en feilmelding? :\

[kaffenils]

Er xact_abort on? Hvis den er det så vil nemlig batchen avbrytes nå slike feil oppstår, og @@error sjekken din vil aldri kjøre.

 

I SQL Server 2005 skal du i stedet bruke try...catch:

 

begin try
begin Tran

If exists(SELECT * FROM Msisdn WHERE Msisdn=@Msisdn ) 
Begin UPDATE Msisdn SET mnc=@mnc, MonthlyTurnover=MonthlyTurnover+@Turnover, CpaBarred=@CpaBarred, ErrorCode=@ErrorCode, LastUpdate=getDate() WHERE Msisdn=@Msisdn
End 
Else 
Begin INSERT INTO Msisdn (Msisdn, Mcc, Mnc, MonthlyTurnover, CpaBarred, Errorcode) VALUES (@Msisdn, @Mcc, @Mnc, @Turnover, @CpaBarred, @Errorcode)
End 

commit tran
end try
begin catch

if XACT_STATE()<>0
begin rollback Tran
	raiserror('BOOOOOM',16,1)
end

end catch

SELECT @Msisdn

 

Jeg har tatt meg frihet til å endre IF EXISTS(...). Du trenger ikke å bruke TOP 1 i EXISTS fordi SQL Server vil "avbryte" spørringen når første rad er funnet.

 

En annen ting er at denne koden potensielt vil kunne feile fordi en annen session kan ha INSERTet msisdn etter at du sjekket at den ikke fantes til ditt INSERT statement kjøres, med mindre du da kjører i SERIALIZABLE isolation level. Derfor har jeg lagt inn en RAISERROR i CATCH slik at systemet ditt får beskjed om at prosedyren feilet.

[Manfred]

Det er nettopp det som skjer, at det kommer 3 iserts samtidig fra hver sin session

 

Løste det forsåvidt programteknisk, men skulle gjerne lært meg mer TSQL, så jeg lot tråden ligge Så kan jeg gå og skrive om litt kode på mandag

 

Jeg fikk beskjed i dag at "det finnes ikke exception handling i sql", så...

 

Takk for godt svar

[kaffenils]

Jeg fikk beskjed i dag at "det finnes ikke exception handling i sql", så...

 

Det kan godt stemme at det ikke finnes noe standardisert exception handling i SQL, men nå benytter jo du deg av T-SQL.

 

Det er nettopp det som skjer, at det kommer 3 iserts samtidig fra hver sin session

 

Blir alle disse tre INSERTene utført via den samme prosedyren?

[Manfred]

Ja, denne prosedyren kjøres inntil 3 ganger samtidig, via forskjellige sessions. Derfor går alle ned i else'n og prøver å inserte...

 

Løste det som sagt programteknisk, ved å kun kjøre den dersom det er en annen forespørsel som kommer inn. Sjekker den mot en statisk variabel, bare... Men alltid greit å vite hvordan det kan løses i T-SQL også...

[kaffenils]

Men alltid greit å vite hvordan det kan løses i T-SQL også...

 

Du kan f.eks. gjøre slik:

 

begin try
begin Tran

INSERT INTO Msisdn (Msisdn, Mcc, Mnc, MonthlyTurnover, CpaBarred, Errorcode) 
select @Msisdn, @Mcc, @Mnc, @Turnover, @CpaBarred, @Errorcode
where not exists(SELECT * FROM Msisdn WHERE Msisdn=@Msisdn ) 

if @@rowcount=0
begin UPDATE Msisdn SET mnc=@mnc, MonthlyTurnover=MonthlyTurnover+@Turnover, CpaBarred=@CpaBarred, ErrorCode=@ErrorCode, LastUpdate=getDate() WHERE Msisdn=@Msisdn
End 

commit tran
end try
begin catch

if XACT_STATE()<>0
begin rollback Tran
end

end catch

SELECT @Msisdn

 

Grunnen til at denne skal virke er at jeg gjør EXISTS validering + INSERT i samme statement. Dermed kan ikke raden oppstå mellom det tidspunktet du sjekker om den finnes til

du faktisk kjører INSERT (slik du har løst det).

 

Hvis statementet ikke medfører en INSERT (@@rowcount=0) så kjøres UPDATE statementet).

 

Sjekk om dette fungerer bedre, og gi beskjed hvis ikke. Har flere måter å løse det på, men disse innebærer "strengere" isolasjonsnivåer.