SQL Server Performance

Lock Owner ID of blocking transaction.

Discussion in 'SQL Server 2008 General DBA Questions' started by Dhana, Jan 18, 2011.

  1. Dhana New Member

    1)Is there a column in SQL server system tables which identifies unique lock owner ID of the blocking transaction. and
    2) Can we provide a Rowlock while creating a Table in SQL server 2008, We are actually migrating sybase to SQL, in Sybase they have used lock datarows during table creation,is there a equivalent way that we can create in sql server 2008.
    Please help me out in this.
    Thanks
    Dhana
  2. RamJaddu Member

    Hi There,
    You can find out owner-id and blocking information in sys.sysprocesses system tables . You should use self join to find this information.
    I don't think you can setup rowlock while creating table but you can use sp_indexoption to enable / disable rowlocks on indexes.
    this will give you good start. http://support.microsoft.com/kb/934005
    Cheers
  3. satya Moderator

    Welcome to the forums.
    There is a subtle difference in the locking behaviour between SYBASE & SQL Server, thought the native RDBMS atomic rules are followed.
    In SQL Server (as you are using 2008) you can take help of Dynamci Management View (DMVs) to know the resource information, in this case run:
    SELECT tl.request_session_id, wt.blocking_session_id, DB_NAME(tl.resource_database_id) AS DatabaseName, tl.resource_type, tl.request_mode, tl.resource_associated_entity_id
    FROM sys.dm_tran_locks as tl
    INNER JOIN sys.dm_os_waiting_tasks as wt
    ON tl.lock_owner_address = wt.resource_address;
    GO
    Yes you can specify the specific locking hints for your application transaction concurrency, see http://msdn.microsoft.com/en-us/library/ms187373.aspx and be careful before you deploy in the production, as it may lead to other performance problems. such as ROWLOCK, means that the lock type won’t esclated up to page or table locks (which would have effected more records than just those being updated). Instead locks will always be at the ROW level.
    See http://www.sql-server-performance.com/articles/per/lock_contention_nolock_rowlock_p1.aspx and http://sankarreddy.com/2010/10/t-sql-tuesday-11-rowlock-hints-are-always-honored-by-sql-server/ links on ROWLOCK hints,

Share This Page