SQL Server Performance Forum – Threads Archive

What is default locking in MSSQL 2000

Hi, What is default locking in MSSQL 2000; I think it is table level locking if I update one row in a table and if don#%92t commit the same table I cant query.
Is there any way to set the ROW level locking ? Regards
Sri

By default, SQL Server uses row level locking for all tables, unless the SQL Query Optimizer determines that a more appropriate locking level. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi Satya ,
So how to changer SQL Query Optimizer uses row level locking for all tables allways Regards
Srini

http://www.sql-server-performance.com/reducing_locks_hints.asp Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi, I don#%92t want give any locking hints what I want is simple ROW level locking only for all my MSSQL server sessions In the SQL Analyzer
Begin TRANS
Update emp where ename=#%92XXXX#%92 where empno=101 –I did not COMMIT this Open another In the SQL Analyzer windows and If say Select * from EMP Its hangs I can#%92t see any DATA, By right I have see the database before the updates statement right Regards
Srini

The link specifies:
If you intend to UPDATE a record, SQL Server will apply what is called an update lock when it first reads the record, and then when you are ready to actually perform the UPDATE, the update lock is changed to an exclusive lock while the update occurs, and then the lock is released. When an update lock is held on a record, it does allow other transactions to see the record, but it prevents the other transaction from acquiring an exclusive lock on it. This is done in order to prevent potential deadlocks. Another transaction can only get an exclusive lock on the record once the update or exclusive lock is removed. When you normally SELECT data from a table, SQL Server will apply a shared lock on the record or records you are examining. A shared lock allows other transactions to read the same records as you, but the other ones can’t modify these records http://www.sql-server-performance.com/lock_contention_tamed_article.asp – about lock contention. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>