locked
when does the update occurs? RRS feed

  • Question


  • Hi guys, if i did an update and there is an After trigger on that update, when will that trigger occur?

    Basically, if i do

    update 1..

    update 2..

    is it guaranteed that the triggers for update 1 is Completed before the second line update 2 occurs?
    Friday, October 29, 2010 12:05 PM

Answers

  •  heys cool. but if we have 2 users (2 different connections) running an update statement which has a trigger, is it guaranteed that the flow will be either 

    and never:

    1. user X update 

    2. user Y update

    3. trigger for user Y update /  trigger for user X update 

    This will not happen. Since when the user X is updating the row, It will take EXCLUSIVE lock on particular record, to complete the 2nd update it needs the "UPDATE" lock(intended lock to prevent other process without taking X lock), so 2nd update will wait until the 1st update is finished. However, here 1st update has an trigger on it, it will maintain like a transaction. So, once the Update statement and triggers are executed, then only it will release all that locks, then the 2nd statement start to execute.
    • Proposed as answer by Naomi N Friday, October 29, 2010 2:59 PM
    • Marked as answer by Kalman Toth Thursday, November 4, 2010 11:44 AM
    Friday, October 29, 2010 2:16 PM
  • This answer is only correct if user X and user Y update the same row. If they update different rows, two instances of the trigger may very well execute concurrently.

    Hugo Kornelis
    SQL Server MVP

    Yes, If both are updating 2 different rows, There won't be any locking. Both triggers might execute concurrently.

    They will be executing in sequential in case of

    1) updating same row.

    2) If the update statement, in where clause there there is no Unique column.

    For example, u have a table like this

     

    create table mynum(ID int unique,ID1 int)
    insert into mynum values (10,10),(20,20)
    

     

    for the below set of updates,triggers execution might be parallel

     

    update mynum set ID = 10 where ID = 10
    update mynum set ID = 10 where ID = 20
    

     

    But for the below set of updates, triggers execution will never be parallel

     

    update mynum set ID = 10 where ID1 = 10
    update mynum set ID = 20 where ID1 = 20
    

     

     

    There is no such pricipal or rule, to first execute this or that, then only other like that. It depends on locking. If the current update statement needs lock on either particular table/page/row/key, if already another process has incompatible locks on same, it will wait.   

    In our table, Id is an unique colum and ID1 is nonunique column. So, when we query using ID column, Engine can decide only one row will satisfy that, so, when it finds a row, it will stop. when we query using ID1 column, as its not an unique column, it needs to scan all rows. 

    When ever 1st statement is executed in both cases, it will take an exclusive lock on 1st record.

    When 2nd statement is executed, in first case, ID column is unique, already 2nd row is satisfying the criteria. so, it won't wait for 1st record and finish its execution. In second case, ID1 column is not-unique. so, its needs to check all rows, so, it will wait for lock release from 1st record.so, once 1st statement execute, then only 2nd statement execute.

    This will also indicates how scans cause blocking and having atleast one unique/key column in where clause will help better.

    Saturday, October 30, 2010 6:12 AM

All replies

  • Yes, if you do two updates from the same connection, and the first update fires a trigger, that trigger will complete before the second update.

    Tom

    • Proposed as answer by Naomi N Friday, October 29, 2010 1:07 PM
    Friday, October 29, 2010 12:29 PM
  • Yes, that's the ACID property of a RDBMS system in transactions.

    Each update will be atomic and happen in an isolation and one by one till the first triggers completes.


    ~Manu
    http://sqlwithmanoj.blogspot.com/
    • Proposed as answer by Naomi N Friday, October 29, 2010 1:07 PM
    Friday, October 29, 2010 12:45 PM
  •  

    heys cool. but if we have 2 users (2 different connections) running an update statement which has a trigger, is it guaranteed that the flow will be either 

    1.user X update

    2. trigger for user X update

    3. user Y update

    4. trigger for user Y update

    or:

     

    1.user Y update

    2. trigger for user Y update

    3. user X update

    4. trigger for user X update

    and never:

    1. user X update

    2. user Y update

    3. trigger for user Y update /  trigger for user X update

     

     

     

    Friday, October 29, 2010 1:29 PM
  •  heys cool. but if we have 2 users (2 different connections) running an update statement which has a trigger, is it guaranteed that the flow will be either 

    and never:

    1. user X update 

    2. user Y update

    3. trigger for user Y update /  trigger for user X update 

    This will not happen. Since when the user X is updating the row, It will take EXCLUSIVE lock on particular record, to complete the 2nd update it needs the "UPDATE" lock(intended lock to prevent other process without taking X lock), so 2nd update will wait until the 1st update is finished. However, here 1st update has an trigger on it, it will maintain like a transaction. So, once the Update statement and triggers are executed, then only it will release all that locks, then the 2nd statement start to execute.
    • Proposed as answer by Naomi N Friday, October 29, 2010 2:59 PM
    • Marked as answer by Kalman Toth Thursday, November 4, 2010 11:44 AM
    Friday, October 29, 2010 2:16 PM
  •  

    ok thanks alot !

    Friday, October 29, 2010 4:29 PM
  • Op 29-10-2010 16:16, ramireddy schreef:

      heys cool. but if we have 2 users (2 different connections) running an update statement which has a trigger, is it guaranteed that the flow will be either

    and never:

    1. user X update

    2. user Y update

    3. trigger for user Y update /  trigger for user X update

    This will not happen. Since when the user X is updating the row, It will take EXCLUSIVE lock on particular record, to complete the 2nd update it needs the "UPDATE" lock(intended lock to prevent other process without taking X lock), so 2nd update will wait until the 1st update is finished. However, here 1st update has an trigger on it, it will maintain like a transaction. So, once the Update statement and triggers are executed, then only it will release all that locks, then the 2nd statement start to execute.

    This answer is only correct if user X and user Y update the same row. If they update different rows, two instances of the trigger may very well execute concurrently.


    Hugo Kornelis
    SQL Server MVP

    Friday, October 29, 2010 9:27 PM
  • This answer is only correct if user X and user Y update the same row. If they update different rows, two instances of the trigger may very well execute concurrently.

    Hugo Kornelis
    SQL Server MVP

    Yes, If both are updating 2 different rows, There won't be any locking. Both triggers might execute concurrently.

    They will be executing in sequential in case of

    1) updating same row.

    2) If the update statement, in where clause there there is no Unique column.

    For example, u have a table like this

     

    create table mynum(ID int unique,ID1 int)
    insert into mynum values (10,10),(20,20)
    

     

    for the below set of updates,triggers execution might be parallel

     

    update mynum set ID = 10 where ID = 10
    update mynum set ID = 10 where ID = 20
    

     

    But for the below set of updates, triggers execution will never be parallel

     

    update mynum set ID = 10 where ID1 = 10
    update mynum set ID = 20 where ID1 = 20
    

     

     

    There is no such pricipal or rule, to first execute this or that, then only other like that. It depends on locking. If the current update statement needs lock on either particular table/page/row/key, if already another process has incompatible locks on same, it will wait.   

    In our table, Id is an unique colum and ID1 is nonunique column. So, when we query using ID column, Engine can decide only one row will satisfy that, so, when it finds a row, it will stop. when we query using ID1 column, as its not an unique column, it needs to scan all rows. 

    When ever 1st statement is executed in both cases, it will take an exclusive lock on 1st record.

    When 2nd statement is executed, in first case, ID column is unique, already 2nd row is satisfying the criteria. so, it won't wait for 1st record and finish its execution. In second case, ID1 column is not-unique. so, its needs to check all rows, so, it will wait for lock release from 1st record.so, once 1st statement execute, then only 2nd statement execute.

    This will also indicates how scans cause blocking and having atleast one unique/key column in where clause will help better.

    Saturday, October 30, 2010 6:12 AM
  •  

    basically i must ensure that this Never occurs:

    1. user X update

    2. user Y update

    3. trigger for user Y update /  trigger for user X update

    my updates will only look like this: update t set colx=10, coly=10, colz=10 where Id=1 where Id is my primary key and it will never change value. will i need to wrap everything within transactions to ensure that the trigger for an update always happens directly after an update (with nothing in between)

    Saturday, October 30, 2010 8:00 AM
  • By keeping a TABLOCK on the update statement, u can block the other user "Y" executing from his update before user "X" update finished. But keeping TABLOCK will prevents even readers also.so, now its very important to keep the transaction very shot. By moving the code in triggers to update procedure itself, will reduce the delay caused by this transactions. this is syntax for keeping TABLOCK

         update TestTable with (TABLOCK)  set ID = 20 where ID = 20

    RCSI(Read committed snapshot isolation level) will allow readers even when TABLOCK is used,as data will be read from old versions... But It will take some extra space to store versioning information. have a look at that one also...

     

    However, these kind of critical decisions should be taken with utmost care. 

    Saturday, October 30, 2010 9:28 AM
  •  

    is it less transaction time if i remove my trigger and instead put that logic in another stored procedure that would be called explicitly directly after each update, then wrap the update in a transaction like this:

    begin transaction

    update table set..

    exec StoreRecords @arg1,@arg2

    commit

    or is it less transaction time if i keep my trigger and do simply: 

    update table with (tablock) set..

    or rather, which is the prefered solution>

    Saturday, October 30, 2010 10:53 AM
  • There is no particularly documentation or something to tell, stored procedure executing seperately is faster or trigger is faster or immediately next query is faster.

     

    But i believe trigger should be faster compared to writing an statement below that or executing a stored procedure, since it will be done in same context, In case of another stored procedure execution, i am expecting some kind of context switching between workers(queries will be given to workers). 

     

    But testing is the best thing... u test with all three scenarios. which is taking less time, use that......... 

    Saturday, October 30, 2010 11:28 AM
  •  

    ok thanks =P

    Saturday, October 30, 2010 1:49 PM