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 |
|
empeda
Starting Member
3 Posts |
 Posted - 2009-06-01 : 09:39:13
|
| Hi All,I am in the process of converting a rather archaic program to use SQL Server 2008. I'm just looking for a little advice on the following problem.The program includes a method that updates a particular column for all rows in a table, which runs on a separate thread to the main application. When a row in the table is being edited by a user, it is pessimistically locked. When this method updates the column, it detects which rows are currently locked by other users and skips them, stores them, and then tries to update them again. It continues in the background until they are all done.Now, this is a conversion, so I appreciate that this may not be the ideal business model, but I would like to just straight convert to start with if possible rather than having to redesign the method.The rows are currently being locked using a HOLDLOCK. I suppose the question here really is: Is this the best lock to use, and how do I detect which rows are locked? The only thing I have managed so far is to locate the table being locked using exec sp_locks and select OBJECT_NAME().Any help or suggestions greatly appreciated. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-01 : 09:47:01
|
Or you could use the SNAPSHOT ISOLATION level?
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
empeda
Starting Member
3 Posts |
Posted - 2009-06-01 : 09:59:58
|
| That's a good suggestion thinking about it - I think it may solve some of the problems described above, in that specific instance anyway.The lock detection problem may still remain though - I do need to pessimistically lock rows when they're being edited. I therefore need to detect whether the row is locked when another user tries to edit it, and inform them that it is lock if it is.A solution to this would be to add a flag column to the table that was set when the row was being edited, but there are quite a few tables I would have to add this to. Is this the only way to get this behaviour, or is there a way to detect a lock on a row?Many thanks. |
|
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2009-06-01 : 12:00:27
|
| Given the limited information provided, and assuming that the column the separatethread is updating has nothing to do with what the users can edit, I would be inclinedto use optimistic locking and application locks. Something along the lines of1. When the user attempts to edit a row:DECLARE @result intEXEC @result = sp_getapplock <TableName + PK>, 'Exclusive', 'Session', 0IF @result <> 0-- Failed to get application lock PRINT 'Row is locked'2. When the user finishes editing the row:EXEC sp_releaseapplock <TableName + PK>, 'Session'There are no long term locks on the data and the application knows which rows are unavailableby the convention of calling sp_getapplock. |
|
|
|
empeda
Starting Member
3 Posts |
Posted - 2009-06-01 : 12:58:34
|
| That's exactly what I'm looking for - thanks Ifor! |
|
|
|
|
|
|
|
|
Detecting a Locked Row - Best Way?