Locking a table in the stored procedure

10-22-2004, 02:53 PM

I want to find out about locking a table. In a stored procedure, First we are selecting a maximum number and incrementing it and inserting into the table. The stored procedure is called many times in a minute. it is inserting the same number more than 1 time. I think this is happening because, before insert statement is running, the stored procedure is called again. I am trying to lock the table after select statement and release it only after the insert statement. Can anyone tell me where i can find the code for locks.

Thank you,

10-22-2004, 05:30 PM
In order to use stored procedures, you must be running a database server, like SQL Server or Oracle. Instead of playing with an incrementing value, why not set up a field in the table with an Identity property (If SQL Server) or using a Sequence if Oracle?

10-23-2004, 09:49 AM
I assume you are working with SQL Server correct? There have been tons of threads on this board regarding record locking..do a search :).

Here is one past thread to get you started regarding SQL Server transactions and row/record locking..it is similiar to the situation you have provided here. This is will give you a better understanding of what you have to do:


EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum