Professional Web Applications Themes

multiple UPDATE commands in transactions - MySQL

Hi Imagine the operations below in pseudo C code on a InnoDB table. In other words a series of UPDATEs happen in transactional mode and there is no commit unless they all succeed (otherwise rollback happens). The questions is whether the effects of earlier UPDATES are seen by subsequent UPDATES. There is no way to verify it directly since any select statements in between updates would return the state of the table before the transaction began. ================================================== ========= transaction_started=0; "start transaction"; transaction_started =1; while(rank++ < limit) { update mytable SET col1=col1+val, rank=value WHERE date < D1 and rank < value ...

  1. #1

    Default multiple UPDATE commands in transactions

    Hi

    Imagine the operations below in pseudo C code on a InnoDB table.
    In other words a series of UPDATEs happen in transactional mode and
    there is no commit
    unless they all succeed (otherwise rollback happens).
    The questions is whether the effects of earlier UPDATES are seen by
    subsequent UPDATES.
    There is no way to verify it directly since any select statements in
    between updates would
    return the state of the table before the transaction began.

    ================================================== =========
    transaction_started=0;
    "start transaction";
    transaction_started =1;

    while(rank++ < limit) {
    update mytable SET col1=col1+val, rank=value WHERE date < D1 and
    rank < value
    update mytable SET rank=value WHERE date >= D1
    }

    if(transaction_started) {
    if(success)
    commit transaction;
    else
    rollback;
    }

    Johna Guest

  2. #2

    Default Re: multiple UPDATE commands in transactions

    >Imagine the operations below in pseudo C code on a InnoDB table. 

    This is in *ONE* transaction, not a lot of separate ones, right?
     

    They should be.
     

    That shouldn't happen, for selects run by the same transaction doing
    the updates will see the changes made by prior updates.

    Gordon Guest

  3. #3

    Default Re: multiple UPDATE commands in transactions

    Yes Sir, 1 transaction only.
    When I mentioned SELECTs for verification I meant a different session
    (connection) to mysql. 
    a transaction
    may influence subsequent UPDATEs that are part of the same transaction,
    right?
    Thanks.

    Gordon Burditt wrote: 
    >
    > This is in *ONE* transaction, not a lot of separate ones, right?

    >
    > They should be.

    >
    > That shouldn't happen, for selects run by the same transaction doing
    > the updates will see the changes made by prior updates.[/ref]

    Johna Guest

  4. #4

    Default Re: multiple UPDATE commands in transactions

    >Yes Sir, 1 transaction only. 
    >a transaction
    >may influence subsequent UPDATEs that are part of the same transaction,
    >right?[/ref]

    Yes. But there's nothing wrong with doing selects in a transaction
    also. For example, the classic check-processing done by a bank:

    Start transaction.

    Add the amount of the check to the destination account balance.
    (If account doesn't exist, add amount to the "lost+found" bank
    internal account instead and log an orphaned deposit, then continue.)

    Subtract the amount of the check to the source account balance.
    (Rollback and bounce check if account doesn't exist.)

    Subtract any per-check service fees from the source account balance.

    If the source account now has a negative balance, rollback and
    bounce the check. (likely involves a select and it needs to see
    the modified data).

    Commit.

    Where any of the "Verify" steps failing leads to a ROLLBACK.

     
    >>
    >> This is in *ONE* transaction, not a lot of separate ones, right?
    >> 
    >>
    >> They should be.
    >> 
    >>
    >> That shouldn't happen, for selects run by the same transaction doing
    >> the updates will see the changes made by prior updates.[/ref]
    >[/ref]


    Gordon Guest

Similar Threads

  1. transactions in multiple action rules
    By Ron Peterson in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: December 21st, 06:42 AM
  2. multiple grids on one page - use single Update/Edit/Cancel commands
    By Iain Porter in forum ASP.NET Data Grid Control
    Replies: 3
    Last Post: December 16th, 10:13 AM
  3. (n00b) One script, multiple transactions
    By Raptor in forum PHP Development
    Replies: 4
    Last Post: October 19th, 12:55 AM
  4. One script, multiple transactions
    By Geoff Berrow in forum PHP Development
    Replies: 1
    Last Post: October 18th, 07:46 PM
  5. CmdExec: Multiple Commands
    By Leo Bateman in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 12:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139