Re: MS SQL 2000, ADO and edit lock

Thats really a loaded question. The answer is "so what if it is ?"

You can "row lock" when you select a record for editing. That is really
dangerous because you then have to write all sorts of nasty code to clear
the lock if the user falls asleep, goes to lunch, goes to a meeting .... and
doesn't complete the transaction. A better approach is to ask yourself this
....

What business transactions do we have that make one user work on the same
record as another user. In general, that should be highly unusual and is a
flaw in procedures that needs looked at first. Once you have eliminated
procedural flaws, you have to ask you self .. ok ..what if I do go to
update a record and someone else has changed it since I read it ?? what do I
do?? (thats why "so what if it is?").

For the very rare number of times that it is going to happen (hopefully),
you just post an exception and let a human figure out what the hell is going
on that 2 clerks are changing the address on the same guy at the same time
... did he call in twice ?? did the secretary make two copies of a
name/address change ??? Is the data entry person a dumb ass and has 2
copies of the application running on her desktop at once??

There are very rare circumstances where it is ok for party A to overlay the
same column that Party B just changed. If its not OK to overlay it (which
your question implies), then somebody better get a human eyeball on it,
because from a business logic standpoint, its an exception and the computer
cant "resolve" it. .. it can "handle" it , but it cant "resolve" it.

Confused ??.. welcome to the world of DBA.

Now .. to answer your question ..

BEGIN TRAN

SELECT column_1, column_2, column_3
FROM authors
WITH (HOLDLOCK, ROWLOCK)
WHERE au_id = '274-80-9391'

/* Do all your stuff here while the record is locked */

COMMIT TRAN


CAUTION !!!! . anyone doing another select statment that includes this row,
will be brought to a dead stop.




.