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 |
|
matty1stop
Starting Member
37 Posts |
 Posted - 2004-06-30 : 16:06:03
|
| I am looking for a way to do exclusive row level locking for tables.Such that if the following statements are executed from one connection (commit omitted) : BEGIN TRANSACTION select * from authors WITH (HOLDLOCK, ROWLOCK) where state = 'CA' and these statements are executed from another connection: SET LOCK_TIMEOUT 0 select * from authors where state = 'UT' select * from authors where state = 'CA'The first query should work, but the 2nd query should fail as a result of the timeout.I can't seem to get this to work. In addition I will also need to block any inserts that may meet the locked criteria.Thanks,Matt |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-30 : 18:07:10
|
| HOLDLOCK applies only for the atomic transaction specified. It's also kind of useless to use the ROWLOCK and HOLDLOCK in this scenario.ROWLOCK issues a shared lock, which again isn't going to do you any good. You would need to specify an exclusive lock, which can only be issued at the table or page level. Well, you're in a bit of trouble then. :) You could place a column on the table called locked. You update the table to be locked, then display the records.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
|
|
|
exclusive row level locking