Locking a row...

[Brad Pears]

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

 

[Rob Blackmore]

You shouldn't really try to maintain a database lock on the row whilst the
user works on the record.

Either add a column to the row the indicate its locked (a "soft lock") which
you set from the relevant stored procedures or have a last modified column
that you set everytime the row is updated. With the latter you compare this
value with what you get when you read the row and what it is when you try to
update the row - if they are different then someone else has updated it and
you can advise the user accordingly.

Kind regards

Rob

 

[Cor Ligthert[MVP]]

Brad,

There are two methods, optimistic concurrency and pessimistic concurrency
checking.

The pessimistic method is the oldest, the trouble with it was that it gave a
lot of trouble for the user and for the developper, because you had to
handle deadlocks as well. Think on an internet user which simple does not
reply after that he has got a page.

It is also a fact that there are (in most cases not all) only few changes
while there is much retrieved.

The optimisic concurrency is in fact the method standard implemented in Net
with generic procedures.

Optimistic concurrency is as Rob describes. The basic method is with all
the columns. But now the method with the "TimeStamp" the 'soft lock' can as
well be used in generic created SQL procedures. I don't know yet how these
optimistic methods are implemented in Linq to SQL.

As you are creating your SQL procedures by hand, then I advice you to use
the TimeStamp, as that is much easier then the all columns method. But you
cannot use that of course as there is no TimeStamp column in your table.

Cor

 

[Michel Posseth [MCP]]

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