Jump to content


Photo

mysql errors and transaction rollback


  • Please log in to reply
7 replies to this topic

#1 langley

langley
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 03 August 2006 - 07:11 PM

Hi,

I have to insert multiple lines of user details into 2 different tables.

I currently have a script that loops through the lines and adds accordingly.

However if there is an error I need to undo all the entries I have made.

Could somebody tell me will transaction rollback help me in this case?
I currently have the following which is not working where the transaction code is out side the loop.
I'm assuming this is wrong but I'm trying to find a way to avoid having to step back through 50 entries for example if the error is produced in the last line

code follows - thanks for any help or info....

$how_many_errors=0;

mysql_query('BEGIN TRANSACTION_NAME');

for($b=0;$b<$blah;$b++){
//do some SQL  queries
if there is an error increment $how_many_errors
}

if($how_many_errors>0) {
mysql_query('ROLLBACK');
}
else {mysql_query('COMMIT');}


#2 CTM

CTM
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 03 August 2006 - 07:23 PM

You should treat the error right way instead of doing a rollback.

#3 shocker-z

shocker-z
  • Members
  • PipPipPip
  • Advanced Member
  • 864 posts
  • LocationNottingham

Posted 03 August 2006 - 07:26 PM

From what i've read this should work for you.. her's some info i found from searching around.

"Do not use transactions (e.g. with InnoDB MySQL tables) with persistent connections.  If your script stops or exits for any reason, your transaction will be left open and your locks will be left on.  You have to reset MySQL to release them (so far as I can figure).  They won't ROLLBACK automatically on error, like they ought to.  When you restart the script, you'll get a new connection, so you can't rollback or commit for the previous script.  Any script with a start transaction, rollback, or commit SQL statement should use regular (not persistent) connections.  Seems like PHP ought to automatically issue a ROLLBACK on any open transactions when a script exits (error or otherwise) without a COMMIT.  ZEND's site has a brief blurb on this.  It's OK to mix/match so you use a persistent connection for the read stuff, but open a new regular connection conditionally (if you have to update, which is usually less often)."

Andy also this link if you havn't seen it yet http://dev.mysql.com.../en/commit.html


Regards
Liam
www: www.ukchat.ws | irc: irc.ukchat.ws chan: #blufudge

#4 langley

langley
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 03 August 2006 - 07:51 PM

CTM  - I realise that but if for whatever reason there is some kind of failiure to complete inserting of ALL users I need to take out any that may have been inserted by the loop thusfar and I'm trying to avoid going back and deleting each set of details from both tables 1 by 1

shocker-z Thanks for the info -  I had a quick read and tried setting autocommit to 0 but it's still not rolling back. I'm finding it pretty confusing to figure out exactly what you can and can't do with transactions. Where you quoted "Do not use transactions (e.g. with InnoDB MySQL tables) with persistent connections." does that mean I have to reconnect for each query? if so it kind of defeats the object of me trying to use rollback. Sorry if these are dumb questions, I'm no master of this stuff!



#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 03 August 2006 - 09:02 PM

The persistent connection comment has to do with the fact that PHP will reuse the connection. Meaning any open transaction will still be open for the next script that uses the connection. The short answer for that is to use mysql_connect() instead of mysql_pconnect().

As far as your question is concerned. If you're saying you simply want to undo all that you've done if everything didn't go according to plan, then rolling back the transaction will be fine. You should make your table types "INNODB" if you'd like to do that.

http://www.innodb.com/ibman.php
http://dev.mysql.com.../en/innodb.html

#6 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 03 August 2006 - 09:06 PM

shoz wouldnt it work if the user set a session with the old entries then use a simple update page

Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#7 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 03 August 2006 - 11:39 PM

shoz wouldnt it work if the user set a session with the old entries then use a simple update page

I don't fully understand what you mean but the main thing to remember is that depending on the degree to which you change the database it becomes very difficult to manually keep track yourself.

In addition, depending on the error that occurs while making the changes it can become even more complicated. If it's critical that multiple entries/changes occur to the database without error, use the INNODB engine and Transactions.

#8 langley

langley
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 04 August 2006 - 12:07 PM

thanks to everyone for their help - as a result it is now working ;D




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users