Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a vague understanding of transactions that it means you can help the server understand that a series of queries are related.

Scenario : Suppose I have a series of queries which creates a user - inserts a user info record, creates an empty profile record and initialises a few other records etc..

If for whatever reason the script were to fail half way through this, what do I need to do to ensure that everything to do with the "creating a user queries" gets undone?

From what I understand reading the MySQL documentation, it as simple as using START TRANSACTION and COMMIT statements.

If that is so, why do so many CMS's neglect to do so? I suppose a common argument against it might be that it is unnecessary for short scripts dealing with only a few record updates, but am I not right in thinking that it is also highly beneficial when you are dealing with replication environments too?

share|improve this question
add comment

2 Answers 2

In MySQL's InnoDB, you may have to start using SavePoints.

SAVEPOINT

Sets a named transaction savepoint with a name of identifier. If the current transaction has a savepoint with the same name, the old savepoint is deleted and a new one is set.

ROLLBACK TO SAVEPOINT

Rolls back a transaction to the named savepoint without terminating the transaction. Modifications that the current transaction made to rows after the savepoint was set are undone in the rollback, but InnoDB does not release the row locks that were stored in memory after the savepoint. (For a new inserted row, the lock information is carried by the transaction ID stored in the row; the lock is not separately stored in memory. In this case, the row lock is released in the undo.) Savepoints that were set at a later time than the named savepoint are deleted.

RELEASE SAVEPOINT

If the ROLLBACK TO SAVEPOINT statement returns the following error, it means that no savepoint with the specified name exists:

ERROR 1305 (42000): SAVEPOINT identifier does not exist

The RELEASE SAVEPOINT statement removes the named savepoint from the set of savepoints of the current transaction. No commit or rollback occurs. It is an error if the savepoint does not exist.

CAVEAT

All savepoints of the current transaction are deleted if you execute a COMMIT, or a ROLLBACK that does not name a savepoint.

A new savepoint level is created when a stored function is invoked or a trigger is activated. The savepoints on previous levels become unavailable and thus do not conflict with savepoints on the new level. When the function or trigger terminates, any savepoints it created are released and the previous savepoint level is restored.

EPILOGUE

Given this explanation of using SavePoints, you answer may be as simple as issuing a SavePoint in appropriate places:

START TRANSACTION;
SQL to Add User
SAVEPOINT addedsuersalready
SQL to Add Other Things
SAVEPOINT addedsuersalready
SQL to Add Additional Things
COMMIT;

This may help mitigate some issues with ROLLBACKs.

Why do most CMS products not allow for such mechanisms? Most CMS products use the dreaded MyISAM storage engine.

For example, Drupal and WordPress usually install MyISAM and plugins usually expect MyISAM tables. I have written posts about recommending the switch to InnoDB.

What's even worse is that more recent plugins for both WordPress and Drupal still make table changes and leave them as MyISAM. Any CMS that uses FullText Indexes cannot convert tables from MyISAM to InnoDB in MySQL 5.5 and prior. MySQL 5.6 just become GA on Feb 05, 2013 and does support FullText Indexing. Give MySQL 5.6 some maturation time before diving in.

share|improve this answer
    
Can you mention how this might be beneficial in a replication environment for example? Perhaps because it helps avoid inconsistency errors in terms of creating a user - since instead of having half a user account and having to clean that up, you end up with logically that user account needing to be created with a full logical execution of the script that builds the user in the DB. –  deed02392 Feb 15 '13 at 12:19
add comment

Introduction to transactions and savepoints

In your scenario "Creating a new user" the basic process could be the following:

BEGIN;
INSERT INTO `users` (`username`, `password`, ...) VALUES ("me", "XXX", ...);
SET @userid := LAST_INSERT_ID();
INSERT INTO `profile_pictures` (`user_id`, `profile_picture_path`)
  VALUES (@userid, '/somewhere/in/the/filesystem.jpg');
COMMIT;

If for whatever reason the process should end in the middle, it would be enough to issue a ROLLBACK; query somewhere before and instead of the COMMIT. All changes would be undone. This is one of the most basic tasks, which can be accomplished by transactions.

The mentioned savepoints by Rolando might come in handy for example if one inserts the row into profile_pictures first, then tries to copy the image to the desired location, and if this should fail one only wants to undo the second insert, but not the first. (If copying the picture fails, there is no reason to cancel the whole user registration.) For this one can define a savepoint after inserting into users, but before inserting into profile_pictures:

BEGIN;
INSERT INTO `users` (`username`, `password`, ...) VALUES ("me", "XXX", ...);
SET @userid := LAST_INSERT_ID();
SAVEPOINT before_profile_picture;
INSERT INTO `profile_pictures` (`user_id`, `profile_picture_path`)
  VALUES (@userid, '/somewhere/in/the/filesystem.jpg');
COMMIT;

In this process one could issue ROLLBACK TO before_profile_picture; to undo the last insert, but leave the inserted user. If one would issue a COMMIT after this rollback to a specific savepoint, the row in users would be saved whereas nothing would be left in profile_pictures.

...and why they are not used widely...

Transactions only make sense in combination with the storage engine InnoDB, as MyISAM simply does not support transactions. The usage of InnoDB and its locking strategy (row-level locking instead of table locking like MyISAM does) in combination with transactions shows some clear advantages, but also some unclear disadvantages.

The question is, why they are not so common, so we concentrate on disadvantages. The most popular disadvantage is deadlocks.

Explanation of Deadlocks

A deadlock occurs when for example two clients try the following queries at the same time:

Client 1

BEGIN;
SELECT * FROM table1 FOR UPDATE; /* Query 1.1 */
SELECT * FROM table2 FOR UPDATE; /* Query 1.2 */

Client 2

BEGIN;
SELECT * FROM table2 FOR UPDATE; /* Query 2.1 */
SELECT * FROM table1 FOR UPDATE; /* Query 2.2 */

I numbered the four queries for clear reference in the following explanation.

Since the database needs to perform queries in any linear order, it could happen that the order is:

Query 1.1
Query 2.1
Query 1.2
Query 2.2

which would be bad. Query 1.1 locks table1 completely. Query 2.1 then locks table2 completely. Now query 1.2 wants to lock table2, but table2 is already locked by Client 2. This query will block and wait for the other client to finish its operation. So query 2.2 is executed, but this one wants to acquire a lock for table1 which is locked by client 1 and so would wait for it to finish. So both clients wait for each other to finish. This will never happen of course. InnoDB detects such situations and automatically rolls back one of the transactions.

The application(!) receives an error and in theory has the responsibility to restart the transaction. Although this runs under "error" a deadlock is a normal situation in transactional databases and restarting a transaction should be more or less normal workflow for applications: "Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again."

How this affects behavior and application development

We found out in the last section that with the introduction of InnoDB and transactions deadlocks can occur, which the application must be able to cope with.

If I say the application must be able to cope with it, I basically mean the developers of the application must have the capability (and motivation...) to cope with it. This is one of the biggest problems transactions have! A lot of average developers see deadlocks as a flaw of the DBMS, which should not occur and they have no clue how to deal with them.

This only changed in the last like 2 to 5 years or so.

Even if the developers of open source projects understood the reasons for the usage of InnoDB and transactions, some deadlocks are kind of tedious and require a lot of effort to solve. One must have a thorough understanding of database transactions, isolation levels, locking and the application itself, to lower the occurrences of deadlocks. Take a look at How to Cope with Deadlocks in the MySQL manual to get a glimpse of what may be necessary to do in those cases. Often this is considered too much effort, when the advantages are not clear and the skills ought to be learned first.

This also results in customers filling support tickets containing problems like "long waiting times" (since transactions need to wait for other transactions to free the locks), "strange errors" (deadlocks) and similar problems. Customers do not and do not want to understand database theory, deadlocks or whatever. They are interested in a fully functional software.

If one has the choice between using transactions, coping with deadlocks, learning skills, analyze the application thoroughly and answering support tickets in any way a non-technical person can understand what is happening, and on the other side just using MyISAM tables which may run fine in most circumstances, I do understand why it took so long and still takes time for most projects to adopt InnoDB with transactions.

I do see the main reason for the slow adoption in social aspects as mere technical aspects. Hope this post could clarify some aspects concerning the usage of transactions and why their usage is not that broad.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.