Obtaining a table lock in SQL Server

[franz]

I need to lock a table in SQL Server. The reason is that I need to insert a value into an identity column (rather than just taking the next sequence number). I have a program that:

1. Sets identity insert on
2. Inserts into the table
3. Sets identity insert off

The problem is that I believe I should lock the table before I turn identity insert on (otherwise, other transactions will fail). I need to ensure that no other operations will take place on the table while I am doing all three of these steps.

I believe I can set the isolation level, but I cannot find any way to establish the lock without executing the update statement itself - which is a nanosecond too late to prevent another user from getting an error.

[rwendel]

Doesn't a Transaction provide table locking?

[franz]

The documentation says
"For repeatable-read consistency, specify HOLDLOCK in the SELECT statement when opening the standard cursor, and issue a BEGIN TRANSACTION statement before the first FETCH statement. Locks are obtained as the data is fetched and are retained until the application issues a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement."

So, I am not worried so much about the time between steps 2 and 3. But until I execute the transaction, the identity insert is on and other table is exposed to an error.

Or does the Begin Transaction check the SQL inside the transaction and automatically acquire all the locks? I have not been able to find documentationt that clarifies this.

[skhanal]

The locks are aquired only when you read the table.

set transaction isolation level serializable

begin tran
--read all rows to lock the table
select col1 from table

insert ...

commit

set transaction isolation level read committed

[franz]

Thanks, I think I have a solution. I basically created two programs that did inserts, one with, and one without, the identityinsert turned on. Ran them both at the same time. After 64000 inserts there were no conflicts. I think that satisfies me that there is some level of isolation provided by the procedure itself, which is all I need (I did NOT set transaction serializable, though I think I will in my production version). This was on a 2 CPU machine, so I think a collision would have happened if it were possible.

[franz]

By the way, I also found that this code absolutely locks the table even though there are no records actually being selected (top 0).

create procedure locktable as
begin
begin transaction foo
set transaction isolation level serializable

select top 0 * from t with(tablockx)
nop:
goto nop
commit transaction foo
end

execute locktable

[rmiao]

Don't think have anything to do with number of cpu on the server.