17. marts 2005 - 10:12Der er
17 kommentarer og 2 løsninger
Lås rækker med SELECT
Hej eksperter :)
Jeg er rendt ind i et sql problem. Jeg ønsker i en transaktion at starte med at låse alle rækker med samme deviceid ud fra et SELECT statement. Grunden til jeg ønsker at låse alle rækker på denne måde er at flere tråde vil tilgå databasen for at tjekke efter nye rækker.
Her er et eksempel, hvor jeg viser hvad jeg ønsker at gøre.
BEGIN TRANSACTION
-- Markere og låse alle rækker med samme deviceid. -- Låsen skal kunne holdes så længe transaktionen stadig er i live. SELECT id, deviceid FROM Table1 WITH (HOLDLOCK, ROWLOCK) WHERE deviceid IN (SELECT TOP 1 deviceid FROM Table1 WITH (READPAST)) ORDER BY id
-- Herefter ønsker jeg evt. at slette nogle af rækkerne - dette er dog ikke problemet :)
COMMIT TRANSACTION
Jeg er godt klar over at sql-statementet ikke er helt gyldigt, men håber det giver en ide om hvad jeg gerne vil lave :)
Men iøvrigt er jeg lidt forvirret over at du bruger WITH (HOLDLOCK, ROWLOCK) og WITH (READPAST) sammen - den første er serializable - den anden virker kun ved readcommitted ??
Det var for at vise hvad jeg ønsker at gøre. Jeg benytter READPAST for at være sikker på at låste rækker ikke bliver læst. HOLDLOCK, ROWLOCK var for at vise at de rækker der bliver selected og skal låses.
Giver det nogen mening.
Vil følgende kunne løse problemet? Jeg er usikker på om den rent faktisk låser rækkerne.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRANSACTION SELECT id, deviceid FROM Table1 WHERE deviceid IN (SELECT TOP 1 deviceid FROM Table1) ORDER BY id
Jeg kender ikke nok til WITH (HOLDLOCK, ROWLOCK) semantik til at kunne sige om det virker. Umiddelbart ville jeg tro at det også ville serialisere uanset brugen af READPAST. Men jeg er ikke sikker.
Du kan sagtens have flere tråde & connections med serializable. Men databasen garanterer at resulatet bliver ligesom hvis SQL koden mellem BEGIN og COMMIt kun udføres af en tråd/connection ad gangen.
HOLDLOCK Equivalent to SERIALIZABLE. (See SERIALIZABLE for more information.)
SERIALIZABLE Equivalent to HOLDLOCK. Makes shared locks more restrictive by holding them until the completion of a transaction (instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether or not the transaction has been completed). The scan is performed with the same semantics as a transaction running at the SERIALIZABLE isolation level.
Så SERIALIZABLE låser mere end bare selected rækker? Hvis ja, er det ikke den optimale løsning. Så må jeg tilbage til READ COMMITTED:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED GO BEGIN TRANSACTION SELECT id, deviceid FROM Table1 WITH (ROWLOCK) WHERE deviceid IN (SELECT TOP 1 deviceid FROM Table1 WITH (READPAST)) ORDER BY id COMMIT TRANSACTION
Må jeg spørge om hvorfor du ønsker at låse dine rækker? Er det bare pga. andre ikke må læse dem samtidigt eller fordi du ønsker at udføre en update på dem?
Det er fordi jeg vil forhindre flere connections i at kunne læse samme rækker. Hver connection må kun læse de rækker, som indeholde samme deviceid.
Grunden er jeg skal have flere windows services til at holde øje med databasen. Så snart der bliver synkroniseret nye poster ind i tabellen, skal servicen submitte dem videre til en web service. Hvis web servicen accepterer dem, skal windows servicen slette dem fra tabellen.
Den største grund til flere connections ikke må læse de samme poster er fordi det er vigtigt at poster bliver videresendt i en bestemt rækkefølge.
Aha... jamen så er det bare at gøre sådan her, nu har jeg bare brugt Northwind
use Northwind begin tran
select * from orders with (UPDLOCK ROWLOCK) where orderid between 10248 and 10250 commit
I din queryanalyser kan kan du får at lave locken køre begin tran og selecten, vent med commit. Nu skal du så åbne en anden process for at teste at det virkelig funker:
select * from orders with (UPDLOCK ROWLOCK) where orderid = 10248 Her vil du se at orderid 10248 ikke kommer frem!!!
Gå så tilbage din oprindelige process der har lavet locken og udfør commit... og vupti så kan du se at process nr. 2 giver dig orderid 10248
Jeg har prøvet dit eksempel af med Northwind databasen, men kan ikke helt få det til at virke. Efter jeg låser rækkerne 10248 - 10250, har jeg i en anden connection prøvet følgende:
Denne giver slet ingen resultater, før jeg committer den først proces: select * from orders with (UPDLOCK ROWLOCK) where orderid = 10248
Denne giver alle resultater: select * from orders
Denne giver alle resultater: select * from orders with (readpast)
Jeg har brug for at få låse x antal rækker, så andre connections kan ignorere dem.. og dermed frit kan arbejde på de andre rækker.
Den med 10248 giver nemlig først at resultat efter at den første bliver committed, det er for at demonstrere locken. Jeg kan godt forstå nu hvad det er u vil, og det er ikke lige det man normalt vil opnå ved locking. Anyway...
Så ville jeg løse det vha. en separet tabel hvori alle id'er som var i brug blev overført til.
Denne tabel ville jeg ajourføre igennem den procedure der giver rækker til brugeren og left joine op imod den for ikke at få dde id'er der er i brug med ud i en select.
Noget ala, hvis din tabel som du vil styre selects på hedder Table
select t.* from Table t left outer join IDinUse i i.id = t.id where i.id is null
Du skal naturligvis lave en lille procedure der kan vedligeholde id'erne i tabellen IDinUse osv... Men det er en relativ nem opgave.
Jeg ved godt det er en smule kreativ måde at benytte locking på... men havde håbet det kunne løse mit problem :)
Ideen med en separat tabel har jeg haft i tankerne. Problemet med denne løsning er at få "frigjort" rækkerne igen, hvis en connection fx skulle gå ned.
Ja der er selvfølgelig lidt ekstra arbejde med den slags specielle tilfælde. Du kan sammen med de id'er som du vil låse også gemme processid
Den kan du få ud vha. select @@spid og evt. lave lidt connectionstat på den. En anden ting du kunne gøre var måske at sætte en max lock på. Og så sige at hvis id'en havde ligget i mere end 60min i tabellen var skulle den frigøres.
Tak for hjælpen! Jeg tror den rigtige løsning er at benytte "låsningstabellen". Det andet var måske en smule for kreativt :)
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
