SQL Server Performance

nowait in SQL Server

Discussion in 'T-SQL Performance Tuning for Developers' started by lzx168, Oct 15, 2004.

  1. lzx168 New Member

    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
  2. satya Moderator

    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.

Share This Page