Locking Users Out of Table

[Tim Morrison]

SQL Server 2000, MSDE 2000

I have a procedure in my application that I would like only one user at a
time to be able to run. Is there a TSQL command I can run that will
esentially lock a set of tables so others cannot access the table until the
user is done with the procedure or until the user disconnects from that
session (in case of a hung app, I would like to lock released)

TIA

--
Tim Morrison

----------------------------------------------------------------------------
----

Vehicle Web Studio - The easiest way to create and maintain your vehicle
related website.
http://www.vehiclewebstudio.com

[Simon Hayes]

"Tim Morrison" <sa***@kjmsoftware.com> wrote in message
news:uGvSb.184669$I06.2024103@attbi_s01...

SQL Server 2000, MSDE 2000

I have a procedure in my application that I would like only one user at a
time to be able to run. Is there a TSQL command I can run that will
esentially lock a set of tables so others cannot access the table until the user is done with the procedure or until the user disconnects from that
session (in case of a hung app, I would like to lock released)

TIA

--
Tim Morrison

-------------------------------------------------------------------------- -- ----

Vehicle Web Studio - The easiest way to create and maintain your vehicle
related website.
http://www.vehiclewebstudio.com

One possibility is to use an application lock - see sp_getapplock and
sp_releaseapplock in Books Online - which your procedure would attempt to
acquire before continuing. Alternatively, you can use a table column as a
flag to indicate whether or not the procedure is already running, but in
this case you have to handle the case of dropped connections yourself.

Simon

[Kristofer Andersson]

Start a transaction, do a select top 1 * from my_table with (tablockx).
Should keep out everyone except those that do "with (nolock)" dirty reads
until your transaction is committed or rolled back. A global var used in the
SP can be used to flag that it is already running.

"Tim Morrison" <sa***@kjmsoftware.com> wrote in message
news:uGvSb.184669$I06.2024103@attbi_s01...

SQL Server 2000, MSDE 2000

I have a procedure in my application that I would like only one user at a
time to be able to run. Is there a TSQL command I can run that will
esentially lock a set of tables so others cannot access the table until the user is done with the procedure or until the user disconnects from that
session (in case of a hung app, I would like to lock released)

TIA

--
Tim Morrison

-------------------------------------------------------------------------- -- ----

Vehicle Web Studio - The easiest way to create and maintain your vehicle
related website.
http://www.vehiclewebstudio.com

[Erland Sommarskog]

Tim Morrison (sa***@kjmsoftware.com) writes:

I have a procedure in my application that I would like only one user at
a time to be able to run. Is there a TSQL command I can run that will
esentially lock a set of tables so others cannot access the table until
the user is done with the procedure or until the user disconnects from
that session (in case of a hung app, I would like to lock released)

First you need to decide. Is it this particular procedure that you
want to serialize, or do you want to lock out any access to the tables?

Simon suggested application locks, and I think this is the best, as
this can be restricted to the procedure alone. The best is to have
a transaction enclosed, so that when you commit or rollback the lock
is released. But if don't want to have the entire procedure within
a transaction, or want to have several transactions, you can also take
out an application lock on session level.

For more details, look up sp_getapplock in Books Online.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp