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 |
|
Aladin
Starting Member
2 Posts |
 Posted - 2007-04-28 : 02:50:31
|
| I have a question related to implementing application locks. We have two idential apllication processes written in C# which call stored procedures. Both processes access the same table, and potentially could race to access the same row. The work involves fetching one record in a table and calling some web service to determine whether it needs to change it, and apply the change if necessary. We want to ensure that only 1 process is "working" on a given record, i.e. if process A has access to the record then process B should let it go, and vice versa.We're thinking of introducing a column in the table called "locked", and create a stored procedure to "lock" and "unlock" a given record.So process A or B will do something like (pseudo code):1- call sp to "lock" the record2- call web service to determine what to do with it3- make update to the record4- call sp to "unlock" the recordFor the store procedure to lock, would the following work:------------------UPDATE some_table WHERE locked = false AND id = @record_idSET locked = true-- see if we acquired the lockIF @@ROWCOUNT == 1 ------------------The update statement checks if the row wasn't "locked", and "locks" it only if it's not "locked".My question is would this work? i.e. would the update statement execute atomically, i.e. test if lock is not acquired, and acquire it by flagging it as "locked"?Thanks,Aladin |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-28 : 20:21:30
|
| If your backend is sql server, then it takes care of locking. You don't have to worry this in your app. |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-04-28 : 22:03:17
|
just make sure you don't use the NOLOCK hint on reads.
www.elsasoft.org |
|
|
|
Aladin
Starting Member
2 Posts |
Posted - 2007-04-29 : 15:24:36
|
quote:
Originally posted by rmiao
If your backend is sql server, then it takes care of locking. You don't have to worry this in your app.
I probably didn't explain the requirement accurately.I realize that SQL Server performs locks (page locks, row locks, table locks), and ensures only one app is making an update, by escalating locks accordingly.What I'm looking for is a way to "flag" a record that it's being worked on, so that my other application processes can move on to a different record. The application is written as a number of deployable components which need to coordinate the work. The work in this case is fetching a record, and flagging it as "owned" or "work in progress", so that other components which also need to perform work, can move on to the next record. The components operate in configurable cycles, I'm trying to build smarts to avoid two components working on the same record, because the work would be redundant. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-29 : 15:54:29
|
| You can create a flag column in the table, app needs to work on the row should check and set flag first. Reset it once done with the work. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-04-29 : 17:51:23
|
Do you have the notion of users in your app? Do you need to know who has the record checked out? if so you'll need more than a flag. maybe a CheckedOutBy column with a userid or something.
www.elsasoft.org |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-04-29 : 18:20:11
|
quote:
My question is would this work? i.e. would the update statement execute atomically, i.e. test if lock is not acquired, and acquire it by flagging it as "locked"?
Yes it would.DavidMProduction is just another testing cycle |
|
|
|
|
|
|
|
|
Application Lock