Record Locking

[notrut]

How do you lock/unlock records in SQL. I want to prevent other users from updating/deleting records when the record is being accessed by someone else.

 

[Gundapaneni]

Hi,
Try this

select * from <table_name> with (tablock,updlock)

The above query will have the table level lock and update lock until the end of the statement or end of transaction.

For more hlep read locking, hints in BOL.

Madhu.

 

[tlbroadbent]

In order to provide any significant advice, we need to know how your application works. At what point do you want to lock rows? When the data is read? When the user does an update?

In my opinion, most applications should allow SQL Server to handle locking. I certainly advise against using tablock. That can make a system nearly unusable in a multi-user environment.

Check the following resources.

Understanding Locking in SQL Server

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_7xde.asp

Customizing Locking with SQL Server

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_5wdu.asp

Dynamic Locking

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_7xde.asp

Customizing Transaction Isolation Level

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_27cc.asp

Transactions, locking and concurrency in SQL Server

http://www.swynk.com/friends/sharma/locking.asp

Inside SQL Server: Controlling Locking

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=6198

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.