none
Readpast, Trigger and DTC Synergy

    Question

  • Please see my code execution flow:

    1. Delete few rows from table1 on ABC database onServer1
    2. Table1 has a After trigger on it which makes a remote server call to a proc on XYZ database on Server1.
    3. XYZ proc has a distributed transaction inside which I am running a Select statment as shown below:

     set transaction isolation level repeatable read                 
    begin tran 
                    declare @tasksPending int 
                    set @sql = '
                                    select @count_out = count(*) from ' + @Server2 + 'dbo.PQR with (READPAST) 
         where Status =1  and ActiveRecord = 1'
                 
       exec sp_executesql @sql, N'@count_out int output', @count_out = @tasksPending output

    Please! note that table dbo.PQR is on Server2 and in some other database.

    Now My delete statement is failing with error:

    Msg 1206, Level 18, State 118, Procedure XYZ, Line 65

    The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

    And when I am executing above steps without READPAST in the above query its working fine also outside the trigger the XYZ proc is executing fine even with READPAST. I want READPAST to be there.

    Please help!


    deepak



    Monday, October 22, 2012 8:34 AM

All replies

  • Hello,

    I have noticed few queries uses ROWLOCK, UPDLOCK and READPAST query hints together. I want to know whats the difference between below 2 queries:

    Case I

    SELECT queries (ROWLOCK, UPDLOCK, READPAST) from table

    Case II

    SELECT queries (READPAST) from table

    Will the Case II query get blocked because of any X lock on the table? We know it Case I query won't be blocked in this case.

    Thanks


    deepak

    Monday, October 22, 2012 9:54 AM
  • BOL:

    1) READPAST

    Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped. That is, the Database Engine skips past the rows instead of blocking the current transaction until the locks are released. For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5. READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table. A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks.

    READPAST can be specified for any table referenced in an UPDATE or DELETE statement, and any table referenced in a FROM clause. When specified in an UPDATE statement, READPAST is applied only when reading data to identify which records to update, regardless of where in the statement it is specified. READPAST cannot be specified for tables in the INTO clause of an INSERT statement. Read operations that use READPAST do not block. Update or delete operations that use READPAST may block when reading foreign keys or indexed views, or when modifying secondary indexes.

    READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. When specified in transactions operating at the SNAPSHOT isolation level, READPAST must be combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

    The READPAST table hint cannot be specified when the READ_COMMITTED_SNAPSHOT database option is set to ON and either of the following conditions is true.

    • The transaction isolation level of the session is READ COMMITTED.

    • The READCOMMITTED table hint is also specified in the query.

    To specify the READPAST hint in these cases, remove the READCOMMITTED table hint if present, and include the READCOMMITTEDLOCK table hint in the query.

    2) ROWLOCK

    Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

    3) UPDLOCK

    Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.

    When UPDLOCK is specified, the READCOMMITTED and READCOMMITTEDLOCK isolation level hints are ignored. For example, if the isolation level of the session is set to SERIALIZABLE and a query specifies (UPDLOCK, READCOMMITTED), the READCOMMITTED hint is ignored and the transaction is run using the SERIALIZABLE isolation level.



    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    Monday, October 22, 2012 11:30 AM
  • Thanks Roberson for the repy.

    I am aware about how above hints works individually.

    Actually, I faced one issue where one distributed transaction was failing(With error:1206 DTC error) when I written below query:

    Select * from T1 with (readpast) -- isolation level is repeatable read

    but same query works fine if I write it like this:

    Select * from T1 with(readpast updlock) -- isolation level is repeatable read

    I wanted to know what exactly (readpast updlock ) did to satify the MSDTC.

    PS:Above queries was executed under a tran.

    Thanks


    deepak

    Monday, October 22, 2012 12:49 PM
  • There isn't any point in using UPDLOCK on a select unless it's inside of a multi-statement transaction, and even then it usually means that you are doing "pessimistic locking", like locking a row for a user while they type in new data, then doing an update and committing it.

    Then READPAST would let you read past someone else's UPDLOCK, but of course you could NOT UPDLOCK the very same row(s).

    ROWLOCK would probably be redundant, as that's what SQL Server starts with anyway, and is generally pretty good about staying with it.

    Josh


    • Edited by JRStern Monday, October 22, 2012 3:15 PM
    Monday, October 22, 2012 3:13 PM
  • Thanks for the reply Josh!

    But in my case select statement is only working when I am using the updlock with readpast for the distributed transaction, otherwise I am getting error:1206


    deepak

    Monday, October 22, 2012 3:15 PM
  • By adding the UPDLOCK to your SELECT statement, you are causing the engine to place U (update) locks on records as opposed to S (shared) locks. Multiple transactions can take S locks on the resource, but only one can take a U lock on that same resource. Given that distributed transaction succeeds with U-lock, you probably have a concurrent process placing or attempting to place locks on the same resource. I suggest using profiler to monitor the Lock events to figure out what's going on.

    Alex

    Monday, October 22, 2012 5:51 PM
  • But in my case select statement is only working when I am using the updlock with readpast for the distributed transaction, otherwise I am getting error:1206

    So using updlock *fixes* the problem?

    Again, I would ask if this is part of a transaction with other statements.

    Otherwise I don't really see how a select is going to fail in a DTC in such a way that locking is going to fix it.

    Do other selects to the same (distributed) tables work any better, without hints?

    Josh

    Monday, October 22, 2012 11:02 PM
  • Please see my code execution flow:

    1. Delete few rows from table1 on ABC database onServer1
    2. Table1 has a After trigger on it which makes a remote server call to a proc on XYZ database on Server1.
    3. XYZ proc has a distributed transaction inside which I am running a Select statment as shown below:

     set transaction isolation level repeatable read                 
    begin tran 
                    declare @tasksPending int 
                    set @sql = '
                                    select @count_out = count(*) from ' + @Server2 + 'dbo.PQR with (READPAST) 
         where Status =1  and ActiveRecord = 1'
                 
       exec sp_executesql @sql, N'@count_out int output', @count_out = @tasksPending output

    Please! note that table dbo.PQR is on Server2 and in some other database.

    Now My delete statement is failing with error:

    Msg 1206, Level 18, State 118, Procedure XYZ, Line 65

    The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

    And when I am executing above steps without READPAST in the above query its working fine, also outside the trigger the XYZ proc is executing fine even with READPAST.

    While I was using the trial and error approach , I found that adding UPDLOCK with readpast works everytime without any error even from inside the trigger call.

    So, I would like to know whats the impact of READPAST,UPDLOCK synergy in the MSTC behaviour.

    Please help!


    deepak

    Tuesday, October 23, 2012 7:48 AM
  • Remove 

    begin tran 


    Tuesday, October 23, 2012 9:11 AM
  • Thanks for the reply!

    I did that, but still I am getting the error , also I need the transaction bcz there are other operations which I need to put inside it.


    deepak

    Tuesday, October 23, 2012 9:13 AM
  • You still aren't showing us the rest of the transaction, there is a commit, right?

    The thing is, do you really need repeatable read?  Anything more than the default read committed is likely to cause a *lot* more locking than you might expect just looking at the code.

    Josh

    Tuesday, October 23, 2012 10:19 AM
  • Rest of the transaction queries are simple insert etc.

    I used repeatable read because READPAST was only working with that isolation level, I know its supposed to work with Read Commited too, but that didnt happen(Select Query was blocking), So I used repeatable read.

    Yeah! there is a commit and rollback (as required) but the execption is happening before those.


    deepak


    Tuesday, October 23, 2012 10:22 AM
  • It is 99% certain that the inserts and the triggers that are really causing the problems, and your use of repeatable read.  If you issue commands that conflict, no use of hints is going to get SQL Server to run past it.  If you don't show those parts of your code, nobody can really guess what is needed.

    It sounds like an interesting curiosity that your query hints including UPDLOCK seem to be *decreasing* the locks that the inserts and repeatable read would be expected to put in.

    How do you know that it was blocking on the select?

    Josh

    Tuesday, October 23, 2012 2:46 PM
  • I updated a row in 'dbo.PQR' under a tran, without commit and then I ran the above mentioned code. with the repeatable isolation level there was no blocking and with read commited above query was blocked.

    The blocking test which I described just now was done with UPDLOCK and READPAST. Without UPDLOCK we are getting the MSTDC error, so no point of blocking.

    As I said, the 1 step of deleting a row from table1 is a simple delete statement which triggers other steps after it. no other query in the process access dbo.PQR.

    Thanks!


    deepak

    Tuesday, October 23, 2012 3:21 PM