Information générale
Forum:
Microsoft SQL Server
Titre:
Determining a row lock
I need to be able to find the next nonlocked row in a table. The scenario i'm trying to accomplish is this :
1. find a record in a table that needs updating and lock it
2. perform some calculations
3. update it
4. commit the changes
5. loop back to step 1 til complete
This update needs to run on multiple PCs simultaneously while other PCs may also be reading the data. I want the program to create a readonly type lock in step 1 such that during steps 2 through 4 the old data can still be read. However, if a second PC is running this same update process, when looking for a record to update, it must not get stuck on a record that's locked and being processed by the first PC.
I know I could add a field to flag that the record is currently being processed. But it seems that in step 1, I should be able to find a record that needs updating AND is not locked.
I've been playing with SELECT ... WITH (UPDLOCK) to lock the record and SELECT ... WITH (READPAST) to skip over the records locked by other PCs, but I can't seem to get this to work properly for me. The locks seem to escalate such that I can't read other rows in the table, or my READPAST doesn't bypass the lock i think i've got.
This doesn't seem that uncommon a problem, i'm thinking there must be a standard way of doing this.
TIA,
Anthony Letts
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement