You must Sign In to post a response.
  • Category: SQL Server

    Shared lock and intent exclusive lock causing Deadlock

    Hi all,
    Currently I am working on deadlock issue which is causing because share lock(s) and intent exclusive lock().
    As we know select query acquire share lock and update query acquire intent exclusive lock.
    Both the statement select and update acquire page level lock and both are in wait state to get lock on each other.
    Our DB architect suggested that use NOLOCK hint with select statement, but data we are fetching from table is so important it should be correct every time and with NOLOCK we cant sure the data accuracy because of dirty read.
    Please let me know if is there any solution for this issue?
  • #765167
    Please find the sample deadlock information.
    We got this information using Extended Event.

    Delete Attachment

  • #765193
    Are you using transaction in SQL server ? if no then you have to.
    There are multiple chances of getting data in dirty reads state, to avoid it you can use Isolation level in transaction
    see below snippet
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    GO
    BEGIN TRANSACTION
    SELECT * FROM publishers
    SELECT * FROM authors
    ...
    COMMIT TRANSACTION

    above queries put row wise lock and avoid dirty read

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]


Sign In to post your comments