SQL Server Performance

Mirroring and record locking

Discussion in 'SQL Server 2005 Database Mirroring' started by kurtfetherolf, Sep 9, 2008.

  1. kurtfetherolf New Member

    Hi all, I am new to mirroring in 2005, so I have a quick question on how SQL Server handles exclusive locks when a database is being mirrored. My client had mirroring set up in High Prtection mode,i.e. with full transaction safety but no witness. My question concerns the holding of exclusive locks on the Prnciple while waiting for the transactions to be written to disk on the mirror. Does the Principle continue to hold an exclusive lock on a record set, even after a commit on the Principle, while waiting for confirmation that the corresponding commit on the Mirror has been sucessfully written? Thanks in advanceKurt
    kurtfetherolf, Sep 9, 2008
    #2

  2. satya Moderator

    Welcome to the forums.Locking mechanism on a transaction is dealt automatically by the SQL server unless the user specifies to hold to a specific period or time such as using LOCKING HINTS.In your case I would suggest to review the locking y using following script:SELECT resource_type, resource_database_id, resource_associated_entity_id, resource_description, request_mode, request_session_id, loginameFROM sys.dm_tran_locks INNER JOIN sysprocesses ON request_session_id = spidORDER BY resource_associated_entity_id ASC;GO
    satya, Sep 12, 2008
    #3

  3. gurucb New Member

    Yes as far as I know it will have to lock the resources till log buffers are flushed at both principal and mirror else there may be data consistency issues between server.
    gurucb, Oct 12, 2008
    #4

Share This Page