Locking a table

[maheshmehta]

Hi Pals
i want to lock a table for a transaction. And within the transaction i have 2 sql statements. One for insert and the other for getting the id of the inserted row.
Eg
Begin transaction
Insert into temp_table values(1,'mahesh')
Select max(id) from temp_table
Commit transactin

What i want is when a user is executing this procedure
no one else could get the same id as this user would get.

Any help would be appreciated.
Mahesh

 

[Gundapaneni]

Hello,

I think below one will solves your problem, you can try this

Begin transaction
Insert into temp_table values(1,'mahesh')
Select max(id) from temp_table with (TABLOCK,HOLDLOCK)
Commit transactin

Or if you want to lock the table exclusively use XLOCK option.

Madhu.

 

[tlbroadbent]

Use tablockx and holdlock to lock the table for the duration of the transaction. You can lock on the insert statement.

Begin Transaction
Insert Into temp_table (tablockx, holdlock)
Values(1,'mahesh')
Select max(id) from temp_table
Commit Transaction
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[maheshmehta]

ok
if i use a begin transaction and commit transaction
for the two statements and at the same time some other use is also doing the same won't that transactin and this transactin create some problem of duplicacy. Means the other transactin should not pick the values of the record inserted by the first transction. How do i make this thing sure that its not happening. Some way to check this out.
B'coz this is the solution that i was also thinking.
????Any help on this

Mahesh

 

[tlbroadbent]

TABLOCKX means EXCLUSIVE lock. HOLDLOCK means hold it until the transaction is done. That means that no one else can read the record until it is freed. The transaction will take a few milliseconds.

By the way, SQL handles locking on its own without the hints. The only time I ever use hints is when I want to do mass updates and prefer to lock the tables instead of rows or pages. In other words, you shouldn't have any problem with two people getting the same ID.

Just make sure the ID column has an index. When you search for Max(ID) on an indexed coloumn in SQL 2000, SQL traverses the Index backward. That makes the seek instantaneous. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.