|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Server lockingI have an issue here. 1. Two users are adding Slips [vouchers] from two different terminals. 2. The Slip numbering scheme goes as follows : TR/00001 , TR/00002.... and so on. 3. When these two users click on the SAVE button simultaneously , the application tries to fetch the next available number. If they press SAVE at the same instant, the select query at both their terminals will return the same available number, replace it and save the slips at both the terminals with the same number - A disaster. I was trying to use the following logic in the application : BEGIN TRANSACTION SELECT TOP 1 slipno FROM DEMATSLP WITH(TABLOCKX) WHERE type='TR' ORDER BY slipno DESC --- Add ONE to the received number --- Replace records in the cursor --- Save the cursor COMMIT TRANSACTION However, the issues are : 1. When one of them successfully places the lock the other cannot place the same lock [that is fine], but any other user of the system shall not be able to view reports either, which is again a disaster. What should be the correct solution? So that both the guys cannot place similar locks and get separate numbers when the other releases the lock, and yet, the other users of the system continue to view reports based on the table. Thanks in advance Abhay use UPDLOCK hint instead of TABLOCKX
Show quote "Abhay Sobti" <stans***@vsnl.com> wrote in message news:%23Sw7hFRDGHA.4080@TK2MSFTNGP09.phx.gbl... > Hi friends, > > I have an issue here. > > 1. Two users are adding Slips [vouchers] from two different terminals. > 2. The Slip numbering scheme goes as follows : TR/00001 , TR/00002.... and > so on. > 3. When these two users click on the SAVE button simultaneously , the > application tries to > fetch the next available number. If they press SAVE at the same > instant, > the select query > at both their terminals will return the same available number, replace > it and save the slips > at both the terminals with the same number - A disaster. > > I was trying to use the following logic in the application : > > BEGIN TRANSACTION > > SELECT TOP 1 slipno > FROM DEMATSLP > WITH(TABLOCKX) > WHERE type='TR' > ORDER BY slipno DESC > > --- Add ONE to the received number > --- Replace records in the cursor > --- Save the cursor > COMMIT TRANSACTION > > However, the issues are : > 1. When one of them successfully places the lock the other cannot place > the > same lock [that is fine], but any other user of the system shall not be > able > to view reports either, which is again a disaster. > > What should be the correct solution? So that both the guys cannot place > similar locks and get separate numbers when the other releases the lock, > and > yet, the other users of the system continue to view reports based on the > table. > > Thanks in advance > Abhay > >
Show quote
"Abhay Sobti" wrote: Let the users issue select statements with the (nolock) optimizer hint ...> Hi friends, > I have an issue here. > 1. Two users are adding Slips [vouchers] from two different terminals. > 2. The Slip numbering scheme goes as follows : TR/00001 , TR/00002.... and > so on. > 3. When these two users click on the SAVE button simultaneously , the > application tries to > fetch the next available number. If they press SAVE at the same instant, > the select query > at both their terminals will return the same available number, replace > it and save the slips > at both the terminals with the same number - A disaster. > > I was trying to use the following logic in the application : > BEGIN TRANSACTION > SELECT TOP 1 slipno > FROM DEMATSLP > WITH(TABLOCKX) > WHERE type='TR' > ORDER BY slipno DESC > --- Add ONE to the received number > --- Replace records in the cursor > --- Save the cursor > COMMIT TRANSACTION > > However, the issues are : > 1. When one of them successfully places the lock the other cannot place the > same lock [that is fine], but any other user of the system shall not be able > to view reports either, which is again a disaster. > What should be the correct solution? So that both the guys cannot place > similar locks and get separate numbers when the other releases the lock, and > yet, the other users of the system continue to view reports based on the > table. > Thanks in advance > Abhay NOLOCK will make your queries return incorrect results at lightning speed.
Show quote "Christian Donner" <ChristianDon***@discussions.microsoft.com> wrote in message news:61853D3B-4FF1-4E07-9ABC-BD2011694C7F@microsoft.com... > "Abhay Sobti" wrote: >> Hi friends, >> I have an issue here. >> 1. Two users are adding Slips [vouchers] from two different terminals. >> 2. The Slip numbering scheme goes as follows : TR/00001 , TR/00002.... >> and >> so on. >> 3. When these two users click on the SAVE button simultaneously , the >> application tries to >> fetch the next available number. If they press SAVE at the same >> instant, >> the select query >> at both their terminals will return the same available number, >> replace >> it and save the slips >> at both the terminals with the same number - A disaster. >> >> I was trying to use the following logic in the application : >> BEGIN TRANSACTION >> SELECT TOP 1 slipno >> FROM DEMATSLP >> WITH(TABLOCKX) >> WHERE type='TR' >> ORDER BY slipno DESC >> --- Add ONE to the received number >> --- Replace records in the cursor >> --- Save the cursor >> COMMIT TRANSACTION >> >> However, the issues are : >> 1. When one of them successfully places the lock the other cannot place >> the >> same lock [that is fine], but any other user of the system shall not be >> able >> to view reports either, which is again a disaster. >> What should be the correct solution? So that both the guys cannot place >> similar locks and get separate numbers when the other releases the lock, >> and >> yet, the other users of the system continue to view reports based on the >> table. >> Thanks in advance >> Abhay > > Let the users issue select statements with the (nolock) optimizer hint ...
Show quote
"Abhay Sobti" <stans***@vsnl.com> wrote in message If all users use the same code to generate slipno's you can serialize them news:%23Sw7hFRDGHA.4080@TK2MSFTNGP09.phx.gbl... > Hi friends, > > I have an issue here. > > 1. Two users are adding Slips [vouchers] from two different terminals. > 2. The Slip numbering scheme goes as follows : TR/00001 , TR/00002.... and > so on. > 3. When these two users click on the SAVE button simultaneously , the > application tries to > fetch the next available number. If they press SAVE at the same > instant, > the select query > at both their terminals will return the same available number, replace > it and save the slips > at both the terminals with the same number - A disaster. > > I was trying to use the following logic in the application : > > BEGIN TRANSACTION > > SELECT TOP 1 slipno > FROM DEMATSLP > WITH(TABLOCKX) > WHERE type='TR' > ORDER BY slipno DESC > > --- Add ONE to the received number > --- Replace records in the cursor > --- Save the cursor > COMMIT TRANSACTION > > However, the issues are : > 1. When one of them successfully places the lock the other cannot place > the > same lock [that is fine], but any other user of the system shall not be > able > to view reports either, which is again a disaster. > > What should be the correct solution? So that both the guys cannot place > similar locks and get separate numbers when the other releases the lock, > and > yet, the other users of the system continue to view reports based on the > table. > using some other resource. For instance you can use sp_getapplock to serialize a transaction across all users without issuing any locks against your tables. EG: BEGIN TRANSACTION exec sp_getapplock @Resource = 'SlipNoMutex', @LockMode = 'Exclusive' SELECT TOP 1 slipno FROM DEMATSLP WITH(TABLOCKX) WHERE type='TR' ORDER BY slipno DESC --- Add ONE to the received number --- Replace records in the cursor --- Save the cursor COMMIT TRANSACTION David Hi
Sorry to 'join' in on this post, but as I was attempted to do something so similar I've used your code. However, if I use: exec @lnLock = sp_getapplock @Resource = 'SlipNoMutex', @LockMode = 'Exclusive' lnLock always returns -999, which is shown as a syntax error. I've checked the SQL on-line help and there appears to be nothing wrong with the code. Any ideas??? Regards
Show quote
"G18LLO" <G18***@discussions.microsoft.com> wrote in message It will return -999 of, inter allia, you try to get a transaction-level lock news:6005BE5B-6C83-426F-87D5-3832CDFF162C@microsoft.com... > Hi > > Sorry to 'join' in on this post, but as I was attempted to do something so > similar I've used your code. However, if I use: > > exec @lnLock = sp_getapplock @Resource = 'SlipNoMutex', @LockMode = > 'Exclusive' > > lnLock always returns -999, which is shown as a syntax error. I've checked > the SQL on-line help and there appears to be nothing wrong with the code. > Any > ideas??? (the default) and you don't have a current transaction. EG BEGIN TRANSACTION declare @rc int exec @rc = sp_getapplock @Resource = 'SlipNoMutex', @LockMode = 'Exclusive' select @rc waitfor delay '00:00:20' print 'woo hoo' COMMIT TRANSACTION Works declare @rc int exec @rc = sp_getapplock @Resource = 'SlipNoMutex', @LockMode = 'Exclusive' select @rc waitfor delay '00:00:20' print 'woo hoo' Fails with -999 David You should probably place a clustered index on slipno, and then use:
--declare @slipno BEGIN TRAN SELECT @slipno = MAX(slipno) FROM DEMATSLP WITH(UPDLOCK, HOLDLOCK) --modify @slipno --write out the changes COMMIT This places an update range-lock on DMATSLP which effectively serializes transactions executing this procedure while allowing other transactions to read the table at the same time. I prefer MAX to TOP 1 because the ORDER BY may cause a sort, whereas MAX will at most require a scan. The clustered index may make that moot, however. You should NEVER use a cursor within a transaction. ABSOLUTELY NEVER!!! (Unless you want to spend the rest of your limited time at your job troubleshooting performance problems caused by excessive blocking and deadlocks....) Show quote "Abhay Sobti" <stans***@vsnl.com> wrote in message news:%23Sw7hFRDGHA.4080@TK2MSFTNGP09.phx.gbl... > Hi friends, > > I have an issue here. > > 1. Two users are adding Slips [vouchers] from two different terminals. > 2. The Slip numbering scheme goes as follows : TR/00001 , TR/00002.... and > so on. > 3. When these two users click on the SAVE button simultaneously , the > application tries to > fetch the next available number. If they press SAVE at the same > instant, > the select query > at both their terminals will return the same available number, replace > it and save the slips > at both the terminals with the same number - A disaster. > > I was trying to use the following logic in the application : > > BEGIN TRANSACTION > > SELECT TOP 1 slipno > FROM DEMATSLP > WITH(TABLOCKX) > WHERE type='TR' > ORDER BY slipno DESC > > --- Add ONE to the received number > --- Replace records in the cursor > --- Save the cursor > COMMIT TRANSACTION > > However, the issues are : > 1. When one of them successfully places the lock the other cannot place > the > same lock [that is fine], but any other user of the system shall not be > able > to view reports either, which is again a disaster. > > What should be the correct solution? So that both the guys cannot place > similar locks and get separate numbers when the other releases the lock, > and > yet, the other users of the system continue to view reports based on the > table. > > Thanks in advance > Abhay > >  |
|||||||||||||||||||||||
Other interesting topics