Ultragames
02-14-2008, 04:25 AM
My original post had a question which I was later able to answer using the MySQL manual.
Should anyone be looking for information on rollingback mysql queries, MySQL transaction are the way to go. You can read about them here (http://dev.mysql.com/doc/refman/5.0/en/commit.html).
Transactions work per connection and not per query. The basic model works like this:
START TRANSACTION;
INSERT INTO my_table VALUES( 'test' );
COMMIT;
or
ROLLBACK;
Now obviously the above example is a little silly. If you are only doing one query, then in almost all cases you should be able to do any validation of the data before running the query. Where the real power of transactions comes in is when doing multiple queries. (Not including SELECTS, which can't be rolled back by their very nature.)
The take for example, this set of queries:
INSERT INTO students VALUES (1, 'Tommy');
INSERT INTO classes_to_students (23, 1);
Now we have associated Tommy with English class. But if later on in the script we find that there is a problem, (or a mysql error) then we may want to change that. Take for example, this:
INSERT INTO class_times VALUES (23, '09:00');
SELECT * FROM class_times ct LEFT JOIN classes_to_students cs ON ct.class_id = cs.class_id WHERE cs.student_id = 1 AND class_time = '09:00';
If our SELECT returns more than one row, then we know that Tommy is now enrolled in two classes, both at 9am. We can't have that, so we must undo the INSERTS. Transactions are great for this.
Now obviously in this example, we could have simply checked to see that Tommy wasn't already in another class at 9am before hand, but this might not always be the case.
A few things to note: Transactions are only available on InnoDB (http://dev.mysql.com/doc/refman/5.0/en/innodb.html), BDB, and NDB Cluster formats. BDB has been removed from recent version of MySQL, and InnoDB is not available with all hosts.
Another thing to note is that, while knowing how to write the queries yourself is always good, PHP does some functions to handle transactions for you in the newish mysqli function set (http://us.php.net/mysqli), should you be using PHP. (autocommit (http://us.php.net/manual/en/function.mysqli-autocommit.php), commit (http://us.php.net/manual/en/function.mysqli-commit.php), and rollback (http://us.php.net/manual/en/function.mysqli-rollback.php))
Should anyone be looking for information on rollingback mysql queries, MySQL transaction are the way to go. You can read about them here (http://dev.mysql.com/doc/refman/5.0/en/commit.html).
Transactions work per connection and not per query. The basic model works like this:
START TRANSACTION;
INSERT INTO my_table VALUES( 'test' );
COMMIT;
or
ROLLBACK;
Now obviously the above example is a little silly. If you are only doing one query, then in almost all cases you should be able to do any validation of the data before running the query. Where the real power of transactions comes in is when doing multiple queries. (Not including SELECTS, which can't be rolled back by their very nature.)
The take for example, this set of queries:
INSERT INTO students VALUES (1, 'Tommy');
INSERT INTO classes_to_students (23, 1);
Now we have associated Tommy with English class. But if later on in the script we find that there is a problem, (or a mysql error) then we may want to change that. Take for example, this:
INSERT INTO class_times VALUES (23, '09:00');
SELECT * FROM class_times ct LEFT JOIN classes_to_students cs ON ct.class_id = cs.class_id WHERE cs.student_id = 1 AND class_time = '09:00';
If our SELECT returns more than one row, then we know that Tommy is now enrolled in two classes, both at 9am. We can't have that, so we must undo the INSERTS. Transactions are great for this.
Now obviously in this example, we could have simply checked to see that Tommy wasn't already in another class at 9am before hand, but this might not always be the case.
A few things to note: Transactions are only available on InnoDB (http://dev.mysql.com/doc/refman/5.0/en/innodb.html), BDB, and NDB Cluster formats. BDB has been removed from recent version of MySQL, and InnoDB is not available with all hosts.
Another thing to note is that, while knowing how to write the queries yourself is always good, PHP does some functions to handle transactions for you in the newish mysqli function set (http://us.php.net/mysqli), should you be using PHP. (autocommit (http://us.php.net/manual/en/function.mysqli-autocommit.php), commit (http://us.php.net/manual/en/function.mysqli-commit.php), and rollback (http://us.php.net/manual/en/function.mysqli-rollback.php))