table locks

[jonsey]

How does one change a lock or can u change a lock from DB type to the row level type on a table for updating purposes??
thanks

 

[dbtech]

To change the lock you need to use locking hints, search BOL for
locking hints and SET TRANSACTION ISOLATION LEVEL

Example to force No locks while reading data from authors table in Pubs database

SELECT au_lname FROM authors WITH (NOLOCK)


dbtech

 

[tlbroadbent]

Most of the time you should let SQL Server handle the locking. It is important to understand locking and the situations where the use of hints is advisable. In addition to the SQL BOL locking topics, I recommend the following. Some were written for SQL 7 but apply to SQL 2000 as well.

SQL BOL: Locking

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

SQL BOL: Understanding Locking in SQL Server

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

Tek-Tips FAQ:
How can I eliminate or reduce locking?

http://msdn.microsoft.com/library/en-us/dnvbpj00/html/avoidlocking.asp

http://www.swynk.com/friends/achigrik/SQL70Locks.asp

http://www.sql-server-performance.com/reducing_locks.asp

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

Terry L. Broadbent - DBA
Computing Links:

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

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.