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 |
|
nicksbrother
Starting Member
3 Posts |
 Posted - 2006-05-26 : 13:41:11
|
| I haven't worked much with locking, but I need to use it now for a project. I have an application, that is running on two different servers, but pulling from one sql table.I pull 10 rows at a time as a batch, loop through the rows, update one column in each row, and then update before committing and moving on to the next batch.The problem is, I need to make sure the application on the second server doesn't grab the same 10 rows as the first batch while they are being read/updated.The locking i am using now stops the records from being updated, but doesn't stop them from being read (selected). So the records are being updated twice.I want to do something like this psuedo code below:select top 10 * from table WITH HOLDLOCK where @@rowlock = notLocked I know there is no such thing as @@rowlock = notLocked , but i am looking for a similar solution.So basically, i want to grab the next 10 rows they are currently not locked, and then lock those rows so nothing else can grab them.Can any one help me?Mark |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-26 : 13:43:25
|
| You will need to add a column to your table that says whether or not the row is locked by the application already.Tara Kizeraka tduggan |
 |
|
|
nicksbrother
Starting Member
3 Posts |
Posted - 2006-05-26 : 13:57:58
|
| I am looking for a solution using lock types. If I use another column, I will still have the same problem. What if both applications go to queue up the same rows at the same time?Right now, there are just 2 applications, but in the future, i may have many more applications pulling from the same table.Thanks for the quick reply though. |
|
|
|
nicksbrother
Starting Member
3 Posts |
Posted - 2006-05-26 : 14:02:55
|
| Is there a lock type that would allow me to lock all the rows I pull in a query, so they cannot be selected or updated by another process, but still allow other processes (queries) to access the rows that were not pulled in the first query? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-26 : 14:08:08
|
| Check out SET TRANSACTION ISOLATION LEVEL in BOL. It will require the use of an additional column in your table though as something has to tell the other applications not to use those rows.Tara Kizeraka tduggan |
|
|
|
|
|
|
|
|
SQL Locking Hell!