Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    13

    Question Unanswered: Performing Atomic multi-operation transactions? [MySQL]

    I need to find a way to ensure that 2 INSERT statements occur succesfully or none at all (atomic multi-operation transaction).
    Specifically - I have a table called "transactions", when a user transfers funds I perform one INSERT for the withdrawl from the source account and one INSERT for the deposit to the target account. Obviously I need to ensure at 100% that either both operations work/are succesfull otherwise I want to ensure neither commit.

    For example - what if the application crashes after running the first INSERT statement and never reaches the second - then we would have loss of funds (not permitted).

    Is this somehow possible in MySQL?
    I need these two operations (INSERT INTO source & INSERT INTO target) to be completly atomic (all or nothing).

    Any help would be greatly appreciated...
    Thanks,

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hmm, have you checked da manual?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2006
    Posts
    13

    Question Is a TRIGGER considered atomic with its associated action? [MySQL]

    I was curious to know if a TRIGGER is considered to be atomic with its associated action...?
    Meaning - if I perform an INSERT into one of my tables that has an associated TRIGGER AFTER INSERT ON, what happens if in the split second after the table INSERT something happens (pc dies, loose connection to db, etc...) and the TRIGGER does not get a chance to process? Will the INSERT still commit without the TRIGGER completing?

    If so - is there a way to guard against this? I need these two operations (INSERT & TRIGGER) to be completly atomic (all or nothing).

    Any help would be greatly appreciated...
    Thanks,

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    MySQL looks at many SQL operations differently depending on the MySQL storage engine currently in use. A given bit of code may (and often does) behave differently when it is executed against a different storage engine.

    The default configuration of MySQL (as it is delivered when first installed) uses MyISAM, which does not support the concept of transactions at all, so there is no issue of ATOMIC or otherwise. If you read the MySQL manual COMMIT section, you'll find a single sentence that gives the key tidbit that you need:
    Quote Originally Posted by MySQL Manual
    If you are using a transaction-safe storage engine (such as InnoDB, BDB, or NDBCLUSTER), you can disable autocommit mode with the following statement:
    Follow the link that r937 gave you, and read carefully. The information that you need is there. Read it carefully and interpret the manual very literally... The manual is almost always correct, especially when you think that it must be kidding!

    -PatP

Posting Permissions

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