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 |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2002-07-11 : 16:32:08
|
| Hiya!How do I get an exclusive lock on the row in a SELECT MAX(ID) FROM Tablename WITH(lock) statement?Sarah Berger MCSD |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-07-11 : 16:43:03
|
| ROWLOCK or XLOCK |
 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-07-11 : 16:49:46
|
| Thanks. I wasn't sure ROWLOCK would work for SELECTs, only updates. BTW, what is XLOCK? It is not a valid locking hint in SQL 7.Sarah Berger MCSD |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-07-11 : 16:52:06
|
| Better test ROWLOCK to be sure. XLOCK is an Exclusive Lock in SQL 2000. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-07-11 : 17:05:51
|
| Rowlock doesn't do it. I am having a concurrency problem. Any other way?Sarah Berger MCSD |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-07-11 : 20:06:24
|
| Hiya!Sorry for the bother, but of course Rowlock doesn't help on a SELECT. What would happen is, I select, get the lock, finish selecting and you get the lock to the same row! What I will have to do is, store the MAX(Keyid) in table, and select it, and then update it to its value + 1 with a rowlock. Then the next select will see the updated value only. I am using this to generate account numbers and order numbers and it is satisfactory.Sarah Berger MCSD |
|
|
|
|
|
|
Row lock in SELECT MAX(field)