SQL Server Performance Forum – Threads Archive

nowait in SQL Server

Hi, I need to simulate Oracle "select … for update nowait" by using readpast and updlock hint. For example: Oracle: select columnA from myTable where columnB = ‘aaa’ for update
SQL Server: select columnA from myTable with ( readpast, updlock ) from myTable where columnB = ‘aaa’ The purpose for this is to synchronize the transactions. I.e. when first transaction perform select for update and get a lock, other transactions can not go through. Do you see any problem with this? Any other suggestions? Thanks

SQL Server can be directed to lock an entire table using the SELECT…table_name (TABLOCK) statement. This performs the same operation as the Oracle LOCK TABLE…IN SHARE MODE statement. This lock allows others to read a table, but prevents them from updating it. By default, this lock is held until the end of the statement. If you also add the keyword HOLDLOCK (SELECT…table_name (TABLOCK HOLDLOCK)), the table lock is held until the end of the transaction. An exclusive lock can be placed on a SQL Server table using the SELECT…table_name (TABLOCKX) statement. This statement requests an exclusive lock on a table. It is used to prevent others from reading or updating the table and is held until the end of the command or transaction. It is similar in function to the Oracle LOCK TABLE…IN EXCLUSIVE MODE statement. SQL Server does not offer a NOWAIT option for any of its explicit lock requests. Reviewhttp://www.sql-server-performance.com/lock_contention_tamed_article.asp link for more information on locking. HTH 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.

]]>