How to avoid concurrency. - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to avoid concurrency.

Author

Topic

snrani
Starting Member

7 Posts

Posted - 2005-07-28 : 04:21:36

I have a table with huge data which is unprocessed and there are few jobs which pick each account in the data that is not processed and update the table record to processed.

What action should i take that the same Account NO is not picked by more than 1 job. Am afraid if more than 1 Job might pick the same Account No.

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 07:16:08

Have a three state field?

Not processed
Being processed
Done processing

When you set its value:

1) Change to Being Processed


UPDATE MyTable 
SET MyStatus = "Being Processed"
WHERE MyStatus = "Not Processed"
      AND MyPK = 'SomePKValue'

If @@ROWCOUNT=0 then someone else got there before you

2) When you are done double check that the status is as expected
[code]
UPDATE MyTable
SET MyStatus = "Done Processing"
WHERE MyStatus = "Being Processed"
AND MyPK = 'SomePKValue'
[code]
If @@ROWCOUNT <> 1 at this point then you have a problem!

Alternatively set all the "To be done" stuff to a Batch Number (representative of your current tasks's batch ID) and then just process those records in the "session"

UPDATE MyTable
SET MyBatchID = 123456
WHERE MyBatchID IS NULL

Kristen

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-28 : 07:59:47

>>What action should i take that the same Account NO is not picked by more than 1 job. Am afraid if more than 1 Job might pick the same Account No.

Hm, why not just to set lock (for example using hint "updlock") ?

snrani
Starting Member

7 Posts

Posted - 2005-07-28 : 08:02:29

Thank you Kristen. But I just wanted to know if SQL server has some internal locks as such where a record could be locked.

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 08:15:48

In that case you can probably just put your processing steps in a TRANSACTION, with appropriate lock hints if necessary (which you ought to be doing anyway so that you can roll them back if something fails). Be careful that the task runs quickly though - if its a SQL Sproc that's probably fine, if its a client application don't allow any user input etc. during the transaction which would allow someone to go out to lunch with a transaction still open!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources