despite what some people think T-SQL has a verry advanced and configurable
locking mechanism
Lock mode Description
Shared (S)
Used for read operations that do not change or update data, such as a
SELECT statement.
Update (U)
Used on resources that can be updated. Prevents a common form of
deadlock that occurs when multiple sessions are reading, locking, and
potentially updating resources later.
Exclusive (X)
Used for data-modification operations, such as INSERT, UPDATE, or
DELETE. Ensures that multiple updates cannot be made to the same resource at
the same time.
Intent
Used to establish a lock hierarchy. The types of intent locks are:
intent shared (IS), intent exclusive (IX), and shared with intent exclusive
(SIX).
Schema
Used when an operation dependent on the schema of a table is executing.
The types of schema locks are: schema modification (Sch-M) and schema
stability (Sch-S).
Bulk Update (BU)
Used when bulk copying data into a table and the TABLOCK hint is
specified.
Key-range
Protects the range of rows read by a query when using the serializable
transaction isolation level. Ensures that other transactions cannot insert
rows that would qualify for the queries of the serializable transaction if
the queries were run again.
http://msdn.microsoft.com/en-us/library/ms175519.aspx
If you need adavanced help about using isolation levels and / or lock
modes in Transact-SQL i recomend you to ask your questions in a SQL server
group
in SQL server programming ther are verry capable MVP`s availlable eager to
help you if you have interesting question for them , Uri Dimant for
instance has been a live saver for me several times with my SQL server
problems .
regards
Michel Posseth MCP
"Brad Pears" <br***@truenorthloghomes.comschreef in bericht
news:uW**************@TK2MSFTNGP02.phx.gbl...
>I have a vb.net 2005 app that asks for a job number. After the job number
has been entered, I run an SQL 2000 stored procedure that selects the
appropriate row from the Jobs table. I do not want to allow another user to
make changes to that data, if another user has alreadyt selected that row
(i.e. row level locking).
How do I lock and check for a locked row in a stored procedure and then in
turn trap that error code in my vb app so I can then provide the
appropriate processing (i.e. inform user and do whatever I need)
Thanks, Brad