Lock any particular record in table?

[JawwadLone]

Hello. I want to lock any record of a table which is currently in use of another user in network, so that other user may not make changes in it. Can any one help me in solving this problem. I have heard that we can lock complete table. But i want to lock one or two records, which are currently in use.


Regards,
Jawwad Rashid Lone.

 

[miq]

HI Jawwad,

basic strategy to achieve this looks soemthing like:

Begin Tran
select * from table1 with ([highlight]updatelock[/highlight]) where condition 

update .......

commit tran

Have a nice day!


B.R,
miq

 

[miq]

hi again,

you don't need to lock your records if you have a column in a table with datatype timestamp.

In this case you only have to read data, store timestamp value and when you decide to writeback the changes just ensure that the timestamp value is the same as it was when you read it.

This strategy is effective when their is a significant gap in term of time between data reading and writing actions.
so, your record won't get locked for all that period.

B.R,
miq

 

[donutman]

I like to have my own datetime field for that purpose, so that it documents the actual time of the update.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[EdwinGene]

You can lock a single record manually with the following code snippet. You will need to have a column in the table you can use as a locking flag. A bit column works very well. You will also need to have a unique value column to identify, and know which identity you want to lock before you get to this code.

Of course, every place you access this table from you will have to go through this code. And, of course, you have to remember to manually unlock the record when you are finished with it. But, at least you can get down to the granularity of a single-record lock.



Declare @IGotTheRecord Int

Set @IGotTheRecord = 0

Update
MyTable
Set
RecLocked = 1
Where
IdentityCol = @DesiredRecordID
and
RecLocked = 0

If @@RowCount > 0
Set @IGotTheRecord = 1

Return @IGotTheRecord