Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-03-21 : 08:09:34
|
| Vyas writes "Hi,I have a stored procedure (SpGetFaxCaseForUser) which reads a table FaxBatchTable (Field = Status). This stored procedure is called from a VB application which is installed in several user work stations. Or you can say, this application continiuosly monitors the table FaxBatchTable for any rows with status = 1 and several instances of this application are active at the same time.Stored Proc contains this statement:select top 1 * from faxbatchtable where status = 1if @@rowcount = 1 -- Process the rowThe problem here is since multiple user workstations have this application installed and all of them monitors this table, sometimes, 2 application instances get the same row, which creates a lot of problems.Can I block this in any way? I tried putting transaction lock level and it solved this problem. But it created other problems as this table is highly used by other applications also and it started giving DEADlock.Is there any other solution?Can I use rowlock?select top 1 * from faxbatchtable with (rowlock) where status = 1 -- Will this solve my issue? " |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-03-21 : 08:18:49
|
| Are you sure that this is the statement that causes the deadlock? And is "status" indexed in any way in the faxbatchtable? How many instancs of your vb-app are active at the same time?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-03-21 : 09:52:03
|
| one solution could be to add a column to your faxbatchtable that would "reserve" the row for a given instance. So the code would change to something like:set rowcount 1update faxBatchTable set ReservedBy = <someValueUniqueToTheCallingInstance> where status = 1set rowcount 0--process the row where ReservedBy = <someValueUniqueToTheCallingInstance> and status = 1then set the status = <processedStatus>This just uses sql servers implicit transactions to insure only one row/user with no collisions and no blocking/deadlocking.Be One with the OptimizerTG |
|
|
|
|
|
|
LOCKING