Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 7 of 7

Thread: Transactions

  1. #1
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    I'm making a wizard, and at the end of the wizard, a whole bunch of stuff is added to the database. The problem is that if one of the queries breaks, then the database is left in a screwed-up state given partial data exists.

    The logical thing to do would be to use a transaction: if a query fails, just do a rollback and all is well. That's great, except I'm relying on an automatically generated ID (auto_increment in MySQL, SERIAL in PostgreSQL which is what I'm using), and I need to commit the transaction in order to get the ID. If I don't, the row isn't inserted, so I don't have an ID. It's quite a paradox.

    What would you do, ideally keeping the notion of transactions? Guessing at the next ID is not desirable.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  2.  

  3. #2
    Senior Member
    Join Date
    Jun 2005
    Location
    Atlanta, GA
    Posts
    4,146
    Member #
    10263
    Liked
    1 times
    Does LAST_INSERT_ID() inside the query not work in this case?

  4. #3
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    It doesn't work because no ID is generated because no row is added.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  5. #4
    Senior Member
    Join Date
    Jun 2005
    Location
    Atlanta, GA
    Posts
    4,146
    Member #
    10263
    Liked
    1 times
    Hmm... So, slightly off-topic, then, since I'm not all that familiar with transactions. The transaction doesn't go through before it's all finished... But the queries *are* being run in the meantime? Just... In a safe space, if you will?

  6. #5
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    I think they're simulated, but since it doesn't actually run, it has no autogenerated ID.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  7. #6
    Senior Member Stylise's Avatar
    Join Date
    Jul 2005
    Location
    Mount Martha, Australia
    Posts
    229
    Member #
    10679
    Could you assign a key at the strat of the wizard that you can use as a session and also in a field so that all the records can be found with it?

  8. #7
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    I eventually got it; you can set the foreign key to deferred and then use [minicode]SET CONSTRAINTS ALL DEFERRED[/minicode] to only check foreign key constraints once the transaction is committed rather than after each query. I ran into the exact same problem making an INSERT rule for a view not two weeks later.

    Still, PostgreSQL is so much better than MySQL. It has features and complies with ANSI standards.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 03:44 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com