Failed transactions

Protato

Hey guys,

I have a few quick questions on running transactions in mysql and php. I'm curious what happens when the script encounters an error outside of the transaction.

For instance I'm dealing with products and inventory, I have a transaction that executes various updates. In between some of the transaction updates I use an item class to pull additional information from the database, the functions in this class make use of

 $result = mysql_query($sql) or die("failed");

What would happen if the query in the item failed and killed the script, not letting the rollback query execute?

Would the transaction rollback after x time? Is x a configurable variable, and I presume if it is it's in the mysql cfg not php? Short of updating the functions used to return false rather then terminating, is there anything I can do to force a roll back if the script terminates before a commit or rollback query?

//Edit
Got a response from another source, I need to change all query's to return false if they crap out.

bastien

I think your transaction is gonna hang. My recommendation is that you remove the or die and handle the error properly. If you are returning values then you should return 0 or false to indicate a failure in that query else continue as normal

MarkR

I assume you mean database transactions.

PHP will close the connection when the page ends, whether normally or by an error. This will cause the database to roll back any transaction in progress.

Unless of course, you're using persistent connections. Don't do that. Ever.

Mark

Sxooter

What MarkR said about persistant connections. 99.999% of the time their evil. The rest of the time they're still evil.

Anyway, are you using innodb tables? And not mixing in myisam? Just making sure.

Even if the php script crashes and doesn't close the connection properly (uncommon as that is) eventually the OS will harvest the stale connection and mysql will roll back the transaction.

Roger_Ramjet

There are provisos in the manual about InnoDB and rolling back transactions.
"InnoDB sometimes rolls back only part of the statement, or the whole transaction.
...
Other errors are mostly detected by the MySQL layer of code (above the InnoDB storage engine level), and they roll back the corresponding SQL statement. Locks are not released in a rollback of a single SQL statement."

To my mind they mean that you should trap all errors and handle rollback yourself and not rely on implicit rollback by the db engine.

In the case of a failed connection you are going to have to check for table locks etc as well as for data corruption. Basic rule of thumb: expect the worst and plan for it.

Sxooter

But how can you be sure you can roll everything back? If your update cascaded over to dozens of other rows in other tables, you might be guessing as to what you need to roll back.

A better answer is that if transactions are important to you, either ensure mysql innodb is capable of rolling yours back, or switch to a database that will roll them back. Oracle, PostgreSQL, DB2, even MSSQL server.

I like MySQL as a text storage engine. I don't trust it with transactions.

csn

Sxooter wrote:
I like MySQL as a text storage engine.

Better than PostgreSQL? If so, why exactly?

RE: persistent connections--IIRC they're not evil when you have tons of memory and tons of traffic. In that case, their marginal perfomance benefit might be noticeable. But at that point, you're likely better off getting more servers.

Sxooter

csn wrote:
Better than PostgreSQL? If so, why exactly?

Not necessarily better, but it needs less maintenance in that role than postgresql (what with vacuuming and analyzing) and it supports individual collations per column. PostgreSQL requires a bit more work using opclasses in the query to sort by a different collation.

Generally, for simple storage engine stuff, MySQL is faster and uses less hard drive space.

RE: persistent connections--IIRC they're not evil when you have tons of memory and tons of traffic. In that case, their marginal perfomance benefit might be noticeable. But at that point, you're likely better off getting more servers.

there are a few instances where they aren't truly evil, but they require a system configured in such a way that the apache server(s) have fewer children, collectively than the db server, and a knowing hand to set them up and test them.

They're certainly an easy way for Joe developer to shoot himself in the foot most the time though.

I've actually used them once or twice with good results, but only in very limited applications.

I prefer to use some external form of pooling connections, like pg_pool.

Roger_Ramjet

csn wrote:
Better than PostgreSQL? If so, why exactly?

Because myisam files are ISAM (Indexed Sequential Access Method) files - the index and data are stored on the same disk cylinder so when the index entry is found data can be read without any further disk seeking. Makes data retrieval very fast. Exactly what mysql was built for.

csn

Sxooter wrote:
Not necessarily better, but it needs less maintenance in that role than postgresql (what with vacuuming and analyzing) and it supports individual collations per column. PostgreSQL requires a bit more work using opclasses in the query to sort by a different collation.

"Individual collations per column" went right over my head! Can somebody explain (or tell me to go RTFM or Google ;P)? What do you mean by "collation."

Also, doesn't PostgreSQL do auto-vacuuming now? MySQL still has the mysqladmin --analyze, etc.

I've been tuned out the past half-year. Been busy with non-tech stuff. 😉

Thanks,
csn@aol.dbnewbie.com

Sxooter

csn wrote:
"Individual collations per column" went right over my head! Can somebody explain (or tell me to go RTFM or Google ;P)? What do you mean by "collation."

Not sure where it is covered in the manual. It means you can have one column sort by, say dutch locale, and another sort by spanish in the same table.

Also, doesn't PostgreSQL do auto-vacuuming now? MySQL still has the mysqladmin --analyze, etc.

Yep. but you still gotta enable row level stats and turn it on. And for certain heavily updated table types, that's still not quite enough.

Glad to see ya back.

csn

Sxooter wrote:
Not sure where it is covered in the manual. It means you can have one column sort by, say dutch locale, and another sort by spanish in the same table.

Ah, I remember now...collations. How would it be done in PostgreSQL--separate tables (e.g. one table for each different language)?

Protato

Hey guys,

Thanks for all the replies, this is an awesome amount of information.

While the idea of the connection getting closed (not using persistent connections; in agreement with the evil) out forcing a rollback is nice, it's the lack of immediate action that scares me. These queries will be spanning across multiple tables and the rows are called with some frequency, so if there are any hanging locks it mucks up portions of the rest of the system (unless I'm mistaken on this?).

It wasn't overly troublesome to change the functions not designed specifically for this script to return false on failure. Now the script can take action on such a failure and rollback, so I think I'm in the clear.

Thanks again for the help.

Sxooter

csn wrote:
Ah, I remember now...collations. How would it be done in PostgreSQL--separate tables (e.g. one table for each different language)?

Ummm, I don't think you can. I think you can have the locale the entire cluster was initialized in, and then use operator classes to treat a column like ASCII (i.e. C locale) and that's it.