Transaction, til at administrere 2 samtidige kald

Hvad er transaction isolation level ?

Svaret er nej. Når select'en kører får du en read/share lås. Først når din update fyres, får du en ekslusiv skrivelås.

Dermed kan to forskellige kald nå at læse samme nr.

Løsningen er ganske simpel. Angiv et låsehint i din select, eller skriv hele proceduren om, så du undgår at have to sql statements.

Låsehints'ne burde være HOLDLOCK & UPDLOCK - du kan se dem i Books Online (installeret sammen med Enterprise Mgr og Query Analyser).

Prøv også at lege med denne

Update nrpool set nr_timestamp=getdate
where nr in (SELECT top 1 Nr FROM NrPool
    WHERE (DATEADD(mi,5, Nr_Timestamp) < GETDATE() and (nr is not null)
    ORDER BY Nr_Timestamp)

trer>

der burde ikke være noget problem ved transaction isolation level serializable

Hvis du bruger default (read committed) så prøv_

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

inden BEGIN TRANSACTION.

Næh, men det er sjældent at folk kører andet end standard level, og det har normalt en negativ performance-effekt at skifte til det niveau.

HOLDLOCK har vel præcis samme effekt som TRANSACTION ISOLATION LEVEL SERIALIZABLE
så jeg kan ikke helt se argumentet ?

(og UPDLOCK duer vel ikke i den her kontekst ??)

Interessant diskussion:

er i enige om at SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
vil løse problemet?
Og ved negativ performence, hvad snakker vi om her, altså tidsmæssig, som jeg skrev bliver den 'kun' kaldt ca 40000 gange i døgnet, men der er selvfølgelig nogle timer om dagen hvor der måske er 5000 kald i timen, antal kald stiger forhåbentlig, da det afhænger af antal besøgende på siden.

Så vil det have en mærkbar negativ performence ved f.eks 20000 kald i timen?

Der vil være signifikant overhead uanset hvordan du løser problemet.

20000 TPH = 333 TPM burde ikke give noget problem

Min hypotese er at SET TRANSACTION ISOLATION LEVEL SERIALIZABLE og WITH (HOLDLOCK)
burde gøre det samme og koste det samme i overhead.

Jeg vil argumentere for SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ved
at det er standard og WITH (NOLOCK) ikke er det.

Men man kunne også argumentere for at WITH(NOLOCK) kun gælder for den
ene SELECT mens man kan glemme at sætte TRANSACTION ISOLATION LEVEL
tilbage hvis man genbruger connection og skal lave noget andet
hvor man ikke har brug for SERIALIZABLE.

arne_v> Du har ret mht UPDLOCK - jeg huskede forkert og mente en XLOCK.

Mht forskellen på en HOLDLOCK verus SET TRANSACTION ISOLATION osv. De har samme effekt, men SET kommandoer har normalt en ekstra bivirkning; Proceduren skal rekompileres og ny query plan laves for hvert kald når SET kommandoen ændrer indstillningerne i fht session-indstilningerne.

I øvrigt - i dit sidste indlæg; Du mener HOLDLOCK og ikke NOLOCK :-) 

I øvrigt, nu jeg lige kigger i BOL: HOLDLOCK og SERIALIZABLE gør blot at share-låsen holdes - dvs. andre kan stadig læse data - og så har vi ikke løst problemet.

Så skal der bruges et låsehint, bør det være XLOCK - og ellers bør man skrive proceduren om så der kun indgår et update statement (som vist ovenfor).

Ja - WITH(HOLDLOCK) selvfølgelig.

Jeg kender ikke SQLServer i dybden, men jeg ville blive overrasket hvis
problemet kunne opstå ved transaction isolation level serializable.

Problemet er f.eks.:

con 1: begin
con 1: select
con 2: begin
con 2: select
con 1: update
con 1: commit
con 2: update
con 2: commit

serializable indikerer repeatable read og hvis con 1 skal kunne gentage
SELECT så kan con 2 ikke lave commit inden con 1 har lavet commit, men
hvis con 2 skal kunne gentage SELECT efter con 1 har lavet commit
hvad så - vil den returnere gamle data ?

Hmmmmm, hvad bliver i enige om, vil serializable virke eller ikke, jeg har ikke prøvet det endnu, da jeg ikke har adgang til den sql-server hjemmefra, men med mindre i bliver enige om at det ikke virker, prøver jeg i morgen på arbejde at indsætte linjen med serializable før transaction

trer>
Update nrpool set nr_timestamp=getdate
where nr in (SELECT top 1 Nr FROM NrPool
    WHERE (DATEADD(mi,5, Nr_Timestamp) < GETDATE() and (nr is not null)
    ORDER BY Nr_Timestamp)

Ovenstående ser umiddelbar fin ud, men hvordan får jeg fat i det nr. som opdateres

Update nrpool set nr_timestamp=getdate
where nr in (SELECT top 1 @Nr=Nr FROM NrPool .......)

Dette virker ikke. Brokker sig over = tegnet

Men pointen med recompile af SP gør selvfølgelig WITH(XLOCK) tiltrækkende.

Jeg tror jeg selv har fundet løsningen på mit sidste spørgsmål

begin transaction

Update nrpool set @Nr=nr, nr_timestamp=getdate
where nr in (SELECT top 1 Nr FROM NrPool
    WHERE (DATEADD(mi,5, Nr_Timestamp) < GETDATE() and (nr is not null)
    ORDER BY Nr_Timestamp)

commit

Burde det ikke virke?, det virker i hver fald i test med en bruger (mig)

Men jeg tester det i morgen, så finder jeg ret hurtig ud af det.

wolker> du laver en select efter din update.

a la

declare @ts datetime
set @ts = getdate()
begin transaction
update NrPool with (holdlock) set Nr_Timestamp = @ts
where  nr in
    (select top 1 nr from nrpool
    WHERE (DATEADD(mi,5, Nr_Timestamp) < @ts
    ORDER BY Nr_Timestamp)

select top 1 @nr = nr from nrpool
WHERE (DATEADD(mi,5, Nr_Timestamp) < @ts
ORDER BY Nr_Timestamp)
commit

Måske skal du have en bedre nøgle til at sikre du får samme nr ud som du lige har brugt - men det er tanken.

trer> virker ikke, det prøvede jeg dagen efter, det skete stadigvæk at 2 bruger fik det samme nummer og jeg gjorde det endda sådan her:

set @@dd=getdate()
update NrPool with (holdlock) set Nr_Timestamp = @dd
where ID in
    (select top 1 ID from nrpool
    WHERE (DATEADD(mi,5, Nr_Timestamp) < @ts
    ORDER BY Nr_Timestamp)

så havde jeg jo ID'en, at selecte på, men stadig, kunne der åbenbart komme en bruger mere ind, og det forstår jeg overhoved ikke.

Men langt om længe, da jeg havde brugt hele dagen på at søge efter en løsning fandt jeg den. Og det er faktisk meget nemt at gøre, man gør således

CREATE PROCEDURE dbo.GB_GetNr
@Nr int outPut,
as
begin transaction
Update NrPool with (holdlock) set @nr=nr, Nr_timestamp = Getdate() where nr in
  (Select top 1 nr from NrPool
    WHERE (DATEADD(mi,5, Nr_Timestamp) < Getdate()
    ORDER BY Nr_Timestamp)
commit

Dejlig kort, og det virker hver gang, det har kørt hele ugen uden fejl.

Men i deler pointene, som tak for jeres bidrag, og fordi i gad at svare, og i hjalp mig trods alt på vej.

arne_v>> lav et svar hvis du vil have nogle point, ellers får trer det hele

svar

"Update NrPool with (holdlock) set @nr=nr, Nr_timestamp...."

Ganske interessant - har ikke tidligere set løsninger hvor man sætter en variabel i en update. Så har jeg også fået lært noget her :-)

Ja det er altid rart at lære noget nyt :-)