TSQL - locking tables manually

seamus

Right, as it says, I need to stick an exclusive lock on a table manually, but I can't seem to find any way to do this in TSQL.

Basically, we have an application running on MSSQL. It has a function in it to add users, but with 600 possible users, this is a pain in the ass. So I'm attempting to script it for logon.

The Users table has an ID column. It's the PK, but it's not an identity, and it's not seeded. So it doesn't increment itself, and I can't use functions like IDENT_INCR. I don't understand why people create tables like this...

So my script basically goes to the Users table, gets the last ID in order to figure out what the next one is, then uses this value to add the new user to two tables, and increment a count value in another table.

I plan to put this all into a stored procedure, but a race condition could still happen.

I need a way to apply an exclusive lock on the users table at the beginning of the transaction, before I get the last ID., then release the lock when I've added the user.

Any ideas?

bonkey

Have a look at at the TABLOCKX and HOLDLOCK query hints.

bonkey

seamus wrote:

The Users table has an ID column. It's the PK, but it's not an identity, and it's not seeded. So it doesn't increment itself, and I can't use functions like IDENT_INCR. I don't understand why people create tables like this...

One reason is because in earlier versions of SQL-Server (6.5, maybe 7.0) there were known issues with the Identity column. You'll often find that people have "holdover hangups" from earlier versions.

Kai

Would it not be possible to create a new table that does auto increment and then populate it with the values from the old table ? Just thinking out loud on this. The new table could be identical apart from the Increment setting on the ID column. Obviously change the table names after it has been created.

Or is there something preventing you from doing this ?

seamus

Thanks bonkey, that looks like exactly what I need. Will need to do a little of reading and testing though to make sure I don't cause hassle...

Would it not be possible to create a new table that does auto increment and then populate it with the values from the old table ? Just thinking out loud on this. The new table could be identical apart from the Increment setting on the ID column. Obviously change the table names after it has been created.

Or is there something preventing you from doing this ?

It's a third-party application that uses the database, so chances are if I did this, the application would have a conniption.

Kai

An unknown Error of Type "Conniption" has Occoured while processing a task. Message details: "Application appears to have had a Conniption. Please get your affairs in order".

amen

why not put it all in a transaction?

you could use begin transaction at the start of the stored procedure
and then at the very end either commit or rollback depending on errors/success etc.

much nicer than using tablelockx etc

seamus

amen wrote:

why not put it all in a transaction?

you could use begin transaction at the start of the stored procedure
and then at the very end either commit or rollback depending on errors/success etc.

much nicer than using tablelockx etc

I did have a look at that, but the users table doesn't get locked until I do the insert.

Because the select max(id) query doesn't lock the table, then there's a (tiny, admittely) chance for a race condition to develop. By locking the table on the select query and releasing it at the end of the transaction, then there's theoretically no chance that another process can insert the ID before I do. That sounds right doesn't it?

bonkey

With the default isolation-level, shared locks should be released immediately when the SELECT finishes, even if its inside a transaction. Thats why your select(max) isn't showing you locks.

It will create locks (you can test this by holding open an exclusive lock on something in teh table and then trying a select max) but they're released as soon as the query completes.

You'd need to up the transaction isolation level to repeatable-read or higher to avoid needing locking-hints. At that point, the select will keep its locks until transaction-end.

bonkey

seamus wrote:

I did have a look at that, but the users table doesn't get locked until I do the insert.

Because the select max(id) query doesn't lock the table, then there's a (tiny, admittely) chance for a race condition to develop. By locking the table on the select query and releasing it at the end of the transaction, then there's theoretically no chance that another process can insert the ID before I do. That sounds right doesn't it?

One way to approach it, avoiding all the hassles, is to mimic the IDENTITY function as follows :

1) Create a table with a single field. T_Identity.MyIdentity
2) In your insert SP, do something like :

begin trans
  update T_identity set my_identity = my_identity + 1
  select @id_val = my_identity from t_identity
  insert into ...
    values (@id_val, ...)
commit trans

Bung some error-handling in there to roll-back in case of failures, et voila. No compiler hints, no transaction-isolation problems, no race conditions. The trick is to start with the update

At a guess, thats what amen was referring to, now that I think about it.