However, they can often be used in a way that makes things more robust on databases that support them, without causing problems in places that do not use them.
For example, in the code I am working on, I want an insert_question_attempt method, which inserts a whole bunch of related data in the database.
Using database transactions you can ensure that, even if an unexpected exception occurs, either all the rows have been inserted and the method completes normally, or nothing is added to the database, and the method end with the exception being thrown.
(And, if you are on MySQL/ISAM that cannot do transactions, then if an exception is thrown you will end up with half the data written, and there is really nothing you can do about that. That is what I mean by less robust in databases that do not support them.)
Now, actually, my situation is not that simple, because I have another method, insert_question_attempt_step, and in pseudocode they look like this:
function insert_question_attempt() {
$DB->insert_record(...)
foreach ($steps as $step) {
}foreach ($steps as $step) {
insert_question_attempt_step($step);
}function insert_question_attempt_step() {
$DB->insert_record(...)
foreach ($data as $name => $value) {
}foreach ($data as $name => $value) {
$DB->insert_record(...);
}Now, if you naively try to use transactions here,
function insert_question_attempt() {
$DB->begin_sql();
try {
try {
$DB->insert_record(...)
foreach ($steps as $step) {
$DB->commit_sql();
foreach ($steps as $step) {
insert_question_attempt_step($step);
}$DB->commit_sql();
} catch ($e) {
} $DB->rollback_sql();
throw $e;
}throw $e;
function insert_question_attempt_step() {
$DB->begin_sql();
try {
try {
$DB->insert_record(...)
foreach ($data as $name => $value) {
$DB->commit_sql();
foreach ($data as $name => $value) {
$DB->insert_record(...);
}$DB->commit_sql();
} catch ($e) {
} $DB->rollback_sql();
throw $e;
}throw $e;
it will not work, because you are not allowed to nest database transactions.
Now, for a long time, at the OU, we have had a nice solution to this that sam invented. The attached patch is me rewriting that to fit into the Moodle 2.0 database API. It implements nested logical transactions, while only using one real database transaction. Using the patch, insert_question_attempt would look like
function insert_question_attempt() {
$transaction = $DB->start_transaction('insert_question_attempt');
try {
try {
$DB->insert_record(...)
foreach ($steps as $step) {
$transaction->commit();
foreach ($steps as $step) {
insert_question_attempt_step($step);
}$transaction->commit();
} catch ($e) {
} $transaction->rollback();
throw $e;
}throw $e;
(and insert_question_attempt_step similarly), and that will work. A call to insert_question_attempt will either insert everything into the database, or nothing will be inserted. The same contract applies if you call insert_question_attempt_step on its own.
The clever bit about this, which you may not immediately notice, is the fact that we are using the $transaction object. The reason for this is that it automatically detects situations where people write silly code like
$transaction = $DB->start_transaction('imlazy');
// Do stuff ...
$transaction->commit();
// Do stuff ...
$transaction->commit();
Suppose an exception is thrown in the middle of // Do stuff ... Then the commit is never called, and there is no call to rollback anywhere. You might be left with a dangling database transactions. Bad.
Well, the $transaction object has a destructor. When you get to the end of the function, the $transaction will be destroyed (unless you return it, in which case it will be destroyed elsewhere). When the $transaction is destroyed, it checks to make sure that it has been committed or rolled back, and if not, it outputs a debugging() message telling you that you forgot, and then does a rollback.
The patch also has code to check to make sure the transactions are nested properly, which is why a string is passed into the constructor.
So, as I say, we have used this for ages at the OU and it is really nice. I would like this in Moodle core, but obviously that requires discussion. Hence this forum post.
(Note, the attached patch is not tested yet, and there are no unit tests, but it should make it clear exactly what I am proposing.)
