|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
UPDLOCK problem with Stacki have a table, which is my stack. from this table i want to select row by row the records. after selection i set a currentuser flag, so no other user will get this record. following procedure does this things for me: ALTER PROCEDURE GetSheetFromStack @UserId INT AS SET NOCOUNT ON Declare @ID INT, @BranchNo INT, @BranchUserCount INT SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN SELECT @ID = WorkStackId, @BranchNo = SheetBranchNo FROM( SELECT TOP 1 tblWorkStack.id AS WorkStackId, tblBranch.BranchNo AS SheetBranchNo FROM tblWorkStack WITH (UPDLOCK, READPAST) INNER JOIN tblBranch ON tblBranch.BranchNo = tblWorkStack.BranchNo WHERE (tblWorkStack.CurrentUser = -1) AND ( Check1 = -1 OR Check2 = -1 ) AND ( Check1 != @UserId AND Check2 != @UserId ) AND tblBranch.CurrentUsers < tblBranch.MaxUser ORDER BY tblBranch.Prio ASC, tblWorkStack.BranchNo ASC, tblWorkStack.SheetNo ASC ) AS WS UPDATE tblWorkStack SET CurrentUser = 4 WHERE [Id] = @ID IF NOT EXISTS( SELECT * FROM tblBranchPerUser WHERE BranchNo = @BranchNo AND UserId = @UserId) BEGIN INSERT INTO tblBranchPerUser(UserId, BranchNo) VALUES(@UserId, @BranchNo) UPDATE tblBranch SET CurrentUsers = CurrentUsers+1 WHERE BranchNo=@BranchNo END SELECT tblWorkStack.id, tblWorkStack.SheetNo, tblWorkStack.BranchNo, tblWorkStack.Filename FROM tblWorkStack WHERE [Id]=@ID COMMIT TRAN the problem is, that i still get rows, which are simoultaniosly accessed by another user, although i made the select with (UPDLOCK, READPAST). Can anyone deliver me some trick to get that stuff working? Thanks alot Tom Tom,
Without going through your code...as I understand it a SHARED lock is compatable with an UPDLOCK while the data is being initially read. An EXCLUSIVE lock is not compatable so other users will not see the data until the lock is released. That being said...an EXCLUSIVE lock can cause blocking issues and should be used with caution and full testing. HTH Jerry Show quote "Tom" <t*@semanticsystem.com> wrote in message news:1128097378.618047.70130@f14g2000cwb.googlegroups.com... > Hi all > > i have a table, which is my stack. from this table i want to select row > by row the records. after selection i set a currentuser flag, so no > other user will get this record. following procedure does this things > for me: > > ALTER PROCEDURE GetSheetFromStack @UserId INT AS > > SET NOCOUNT ON > > Declare @ID INT, @BranchNo INT, @BranchUserCount INT > > SET TRANSACTION ISOLATION LEVEL READ COMMITTED > > BEGIN TRAN > > SELECT @ID = WorkStackId, @BranchNo = SheetBranchNo > FROM( > SELECT TOP 1 tblWorkStack.id AS WorkStackId, tblBranch.BranchNo AS > SheetBranchNo > FROM tblWorkStack WITH (UPDLOCK, READPAST) INNER JOIN tblBranch ON > tblBranch.BranchNo = tblWorkStack.BranchNo > WHERE (tblWorkStack.CurrentUser = -1) > AND ( Check1 = -1 OR Check2 = -1 ) > AND ( Check1 != @UserId AND Check2 != @UserId ) > AND tblBranch.CurrentUsers < tblBranch.MaxUser > ORDER BY tblBranch.Prio ASC, tblWorkStack.BranchNo ASC, > tblWorkStack.SheetNo ASC > ) AS WS > > UPDATE tblWorkStack SET CurrentUser = 4 WHERE [Id] = @ID > > IF NOT EXISTS( SELECT * FROM tblBranchPerUser WHERE BranchNo = > @BranchNo AND UserId = @UserId) > BEGIN > INSERT INTO tblBranchPerUser(UserId, BranchNo) VALUES(@UserId, > @BranchNo) > UPDATE tblBranch SET CurrentUsers = CurrentUsers+1 WHERE > BranchNo=@BranchNo > END > > SELECT tblWorkStack.id, tblWorkStack.SheetNo, tblWorkStack.BranchNo, > tblWorkStack.Filename FROM tblWorkStack WHERE [Id]=@ID > > COMMIT TRAN > > the problem is, that i still get rows, which are simoultaniosly > accessed by another user, although i made the select with (UPDLOCK, > READPAST). Can anyone deliver me some trick to get that stuff working? > > Thanks alot > > Tom > yes that might be, but my problem is that NOTHING is locked, means it
happens that 2 users are getting the record parallel... thnx Do they have a WITH (NOLOCK) hint in the select
http://sqlservercode.blogspot.com/ Show quote "Tom" wrote: > yes that might be, but my problem is that NOTHING is locked, means it > happens that 2 users are getting the record parallel... > > thnx > > No, the above stored procedure is exactly the code executed and there
is no NOLOCK.... Tom On 30 Sep 2005 09:22:58 -0700, Tom wrote:
Show quote >Hi all Hi Tom,> >i have a table, which is my stack. from this table i want to select row >by row the records. after selection i set a currentuser flag, so no >other user will get this record. following procedure does this things >for me: > >ALTER PROCEDURE GetSheetFromStack @UserId INT AS > >SET NOCOUNT ON > >Declare @ID INT, @BranchNo INT, @BranchUserCount INT > >SET TRANSACTION ISOLATION LEVEL READ COMMITTED > >BEGIN TRAN > >SELECT @ID = WorkStackId, @BranchNo = SheetBranchNo >FROM( > SELECT TOP 1 tblWorkStack.id AS WorkStackId, tblBranch.BranchNo AS >SheetBranchNo > FROM tblWorkStack WITH (UPDLOCK, READPAST) INNER JOIN tblBranch ON >tblBranch.BranchNo = tblWorkStack.BranchNo > WHERE (tblWorkStack.CurrentUser = -1) > AND ( Check1 = -1 OR Check2 = -1 ) > AND ( Check1 != @UserId AND Check2 != @UserId ) > AND tblBranch.CurrentUsers < tblBranch.MaxUser > ORDER BY tblBranch.Prio ASC, tblWorkStack.BranchNo ASC, >tblWorkStack.SheetNo ASC >) AS WS > >UPDATE tblWorkStack SET CurrentUser = 4 WHERE [Id] = @ID > >IF NOT EXISTS( SELECT * FROM tblBranchPerUser WHERE BranchNo = >@BranchNo AND UserId = @UserId) >BEGIN > INSERT INTO tblBranchPerUser(UserId, BranchNo) VALUES(@UserId, >@BranchNo) > UPDATE tblBranch SET CurrentUsers = CurrentUsers+1 WHERE >BranchNo=@BranchNo >END > >SELECT tblWorkStack.id, tblWorkStack.SheetNo, tblWorkStack.BranchNo, >tblWorkStack.Filename FROM tblWorkStack WHERE [Id]=@ID > >COMMIT TRAN > >the problem is, that i still get rows, which are simoultaniosly >accessed by another user, although i made the select with (UPDLOCK, >READPAST). Can anyone deliver me some trick to get that stuff working? The problem is that neither share locks nor update locks are held at the end of a SELECT statement in READ COMMITTED isolation level. You can fix this in two ways: 1. Acquire an exclusive lock instead of an update lock on the SELECT, or 2. Choose a higher isolation level (I think that REPEATABLE READ is sufficient in your case, but try SERIALIZABLE if you still have problems). Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) > The problem is that neither share locks nor update locks are held at the I don't think this is true, Hugo. I tested this script:> end of a SELECT statement in READ COMMITTED isolation level. SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN SELECT * FROM [LABOR] WITH(UPDLOCK,READPAST) WHERE [Date On] BETWEEN '2005-04-01' AND '2005-04-20' WAITFOR DELAY '00:00:30' EXEC sp_lock @@SPID ROLLBACK The results of sp_lock shows that update locks are still being held even after 30 seconds have passed. I'm not positive, but I think update locks are held for the duration of the transaction, the same as exclusive locks. Shared locks, on the other hand, aren't even held for the duration of the select statement if the transaction isolation level is READ COMMITTED. The locks walk through while the read is executing. Show quote "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:3bbrj1pkgdpmpdns720psslpjsj1p47e12@4ax.com... > On 30 Sep 2005 09:22:58 -0700, Tom wrote: > >>Hi all >> >>i have a table, which is my stack. from this table i want to select row >>by row the records. after selection i set a currentuser flag, so no >>other user will get this record. following procedure does this things >>for me: >> >>ALTER PROCEDURE GetSheetFromStack @UserId INT AS >> >>SET NOCOUNT ON >> >>Declare @ID INT, @BranchNo INT, @BranchUserCount INT >> >>SET TRANSACTION ISOLATION LEVEL READ COMMITTED >> >>BEGIN TRAN >> >>SELECT @ID = WorkStackId, @BranchNo = SheetBranchNo >>FROM( >> SELECT TOP 1 tblWorkStack.id AS WorkStackId, tblBranch.BranchNo AS >>SheetBranchNo >> FROM tblWorkStack WITH (UPDLOCK, READPAST) INNER JOIN tblBranch ON >>tblBranch.BranchNo = tblWorkStack.BranchNo >> WHERE (tblWorkStack.CurrentUser = -1) >> AND ( Check1 = -1 OR Check2 = -1 ) >> AND ( Check1 != @UserId AND Check2 != @UserId ) >> AND tblBranch.CurrentUsers < tblBranch.MaxUser >> ORDER BY tblBranch.Prio ASC, tblWorkStack.BranchNo ASC, >>tblWorkStack.SheetNo ASC >>) AS WS >> >>UPDATE tblWorkStack SET CurrentUser = 4 WHERE [Id] = @ID >> >>IF NOT EXISTS( SELECT * FROM tblBranchPerUser WHERE BranchNo = >>@BranchNo AND UserId = @UserId) >>BEGIN >> INSERT INTO tblBranchPerUser(UserId, BranchNo) VALUES(@UserId, >>@BranchNo) >> UPDATE tblBranch SET CurrentUsers = CurrentUsers+1 WHERE >>BranchNo=@BranchNo >>END >> >>SELECT tblWorkStack.id, tblWorkStack.SheetNo, tblWorkStack.BranchNo, >>tblWorkStack.Filename FROM tblWorkStack WHERE [Id]=@ID >> >>COMMIT TRAN >> >>the problem is, that i still get rows, which are simoultaniosly >>accessed by another user, although i made the select with (UPDLOCK, >>READPAST). Can anyone deliver me some trick to get that stuff working? > > Hi Tom, > > The problem is that neither share locks nor update locks are held at the > end of a SELECT statement in READ COMMITTED isolation level. > > You can fix this in two ways: > 1. Acquire an exclusive lock instead of an update lock on the SELECT, or > 2. Choose a higher isolation level (I think that REPEATABLE READ is > sufficient in your case, but try SERIALIZABLE if you still have > problems). > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) On Fri, 30 Sep 2005 20:57:38 -0400, Brian Selzer wrote:
>> The problem is that neither share locks nor update locks are held at the Hi Brian,>> end of a SELECT statement in READ COMMITTED isolation level. > > >I don't think this is true, Hugo. I tested this script: (snip) As always with SQL Server, the truth is in the testing. Thanks for correcting my mistake and expanding my knowledge, Brian! Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics