The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Well, I have to say that I'm more used to Oracle, postgresql, sqlite and ms sql server than mysql.
In fact, I haven't touched to mysql in the last 6 years...

I don't know the way mysql handle this, and especially not which table engine plugin handle in which way, but I know that some mysql engines simply don't implements transaction.
Start transaction and roolback/commit probably don't trigger any error, but may not do anything either.

What I know, is that in those other engines, if you have 1 statement that fails in a sequence, then the whole transaction (even the queries that had no problems) is rolled back, and that makes plain sense.

What I know, is that in those other engines, if you have 1 statement that fails in a sequence, then the whole transaction (even the queries that had no problems) is rolled back, and that makes plain sense.

this is not true. if the SQL server returns an error on a single query, it will NOT automatically rollback the whole transaction. rollbacks only happen automatically if the connection is closed without an explicit COMMIT or if the server crashes before a COMMIT.

this is not true. if the SQL server returns an error on a single query, it will NOT automatically rollback the whole transaction. rollbacks only happen automatically if the connection is closed without an explicit COMMIT or if the server crashes before a COMMIT.

Got it. So I don't really have to use ROLLBACK explicitly if my connection closes, which it does. But it's nice to have for completeness, which probably clears up some logs?

it's good to include it for completeness, and it might prevent a bug in the future. for example, an modular component of your application may fault and want a rollback. if your use this component inside another component that handles errors in a manner other than immediately terminating, then the transaction might get committed.

rollbacks only happen automatically if the connection is closed without an explicit COMMIT or if the server crashes before a COMMIT.

I was rather thinking about a single statement that updates thousands of rows.
If one of the constraints placed on a column fires, then everything is rolled back.
Now, if you use a cursor, open a transaction, fires the query, commit it and go to the next row, then of course only the rows where an error occurred would stay unchanged.
But at the cost of a lousy query plan and much unneeded strain on the server, if you don't know why you use it that way.

tripy, i understand now what you were trying to say. you're describing statement atomicity: the whole statement works, or it doesn't. however, one statement failing with an error will not roll back a transaction. transactions and statements are not the same thing.

(and to make the distinction even muddier, innodb internally wraps every statement in a transaction to achieve statement atomicity, but that is not the only way to do it)