Re: How can I minimize deadlocks ??
From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 04/19/04
- Next message: DW: "Counting Nulls"
- Previous message: Mike Kanski: "gENERATE ddl FOR DEFAULT VALUES ON THE TABLE"
- In reply to: Andrei: "How can I minimize deadlocks ??"
- Next in thread: Andrei: "Re: How can I minimize deadlocks ??"
- Reply: Andrei: "Re: How can I minimize deadlocks ??"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 19 Apr 2004 15:31:00 -0400
You can do this without a transaction:
update Documents
set
Available = 0
where
DocID = (select top 1 DocID from Documents as d
where d.Available = 1
order by d.DateTimeInserted)
-- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql "Andrei" <anonymous@discussions.microsoft.com> wrote in message news:A7321096-47A3-463E-A95E-D23012A0752F@microsoft.com... Hi everyone, I have an application used by several users. The following logic is implemented in a stored procedure to make sure that the users are accessing the next available document and no documents are accessed by more than one user: Begin Transaction Select Top 1 @DocID = doc.DocID >From Documents With (UpdLock) Where Available = 1 Order By doc.DateTimeInserted if @@RowCount <> 0 Update Documents Set Available = 0 Where DocID = @DocID Commit Transaction This is working fine and I have achieved the goals described above. However we are experiencing quite a high number of deadlocks. Could somebody tell us if I can do something to minimize the number of deadlocks? Thank you in advance.
- Next message: DW: "Counting Nulls"
- Previous message: Mike Kanski: "gENERATE ddl FOR DEFAULT VALUES ON THE TABLE"
- In reply to: Andrei: "How can I minimize deadlocks ??"
- Next in thread: Andrei: "Re: How can I minimize deadlocks ??"
- Reply: Andrei: "Re: How can I minimize deadlocks ??"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
