View Full Version : Committing a MySQL Connection

Joseph Witchard
01-20-2010, 12:07 AM
What exactly does that do? For example:


What purpose does it serve?

01-20-2010, 12:11 AM
If you're not using autocommit and you're using a transactional type table (INNODB for example, not MYISAM), you can actually rollback you're changes in a database. Once commit is called, the temporary records that where the *old* records are removed.

Joseph Witchard
01-20-2010, 08:12 AM
You mean if I edit a table and don't commit, the edit might not happen?

01-20-2010, 01:52 PM
Sort of. If it works like oracle, the transaction is valid for the current connection, and no other connection can view the changes until its been committed. This gives you the opportunity to rollback if you desire. Should you're script terminate abnormally, I'd suspect that mysql works just like oracle and rolls the transactions back.

For the above reason, transactions are very very handy (and also very very dangerous). Consider removing money from one bank account and adding it to another. If you commit you're transaction after adding subtracting from the first account and before adding to the other account and the engine dies, you will have one account short money and one account without being added to. Rerunning the query results in a double subtraction from the originating account with only a single addition to another account.

BTW, in MySQL the autocommit is on by default if I'm not mistaken. You have to explicitly disable it to start transactional processing. You can check with a SELECT @@autocommit. If its 0, its disabled.

01-21-2010, 12:11 AM
For the above reason, transactions are very very handy (and also very very dangerous).

They can also be a bit of an annoyance on occasion too. :D The PostgreSQL documentation has some good info on transactions. Might be worth having a look on their site.

Joseph Witchard
01-21-2010, 02:41 AM
If you set MySQLi Autocommit as soon as you open the connection, would that take care of all queries? Do you need to commit SELECT queries?