Welcome Guest, Not a member yet? Register   Sign In
Trying to understand CI Transactions
#1

[eluser]Unknown[/eluser]
Is there anyone who is using the database transaction functionality in CodeIgniter who can explain how it's intended to be used? After reading through the source I can't think of how it could possibly be useful in the default configuration, so presumably I'm missing something, as somebody had to design it that way for a reason.

Coming from a database background, my typical use case for transactions is high concurrency. For example, say I have these two tables:

CREATE TABLE users (
user_id integer PRIMARY KEY AUTO_INCREMENT,
user_name varchar(64)
);

CREATE TABLE userkeys (
user_id integer NOT NULL,
url_key varchar(32),
expires integer,
);


When a new user is created, the following sequence happens:

INSERT INTO users (user_name) VALUES ('name');
// Get insert ID and store it in newID
INSERT INTO userkeys (user_id, url_key, expires) VALUES (newID, new_generated_key, NOW() + 864000);

Along the way, additional keys may be added to userkeys -- a basic one-to-many relationship. Since there's an expires field, I have a job that runs every once in a while that does this:

DELETE FROM userkeys WHERE expires < NOW();
// Now clean up any users that no longer have any valid keys
SELECT u.user_id FROM users u LEFT JOIN urlkeys k USING (user_id) WHERE u.user_id IS NULL;
// Save list of IDs into a variable
DELETE FROM users WHERE user_id IN (users_to_delete);

Now, there's one problem with this example, and that's that there is a race condition. If a new user is being added while the cleanup is in progress, there's a possibility that the second part of the cleanup will run after the INSERT into users, but before the INSERT into userkeys, and remove the user that was just added since no keys yet reference it.

One easy solution to this is to simply wrap both sets of statements in transactions. How exactly this happens is database dependent. PostgreSQL in Read Committed mode uses a virtual snapshot of the database at the start of each statement that is not affected by concurrent uncommitted transactions. PostgreSQL in Serializable mode uses a snapshot made at the start of the transaction, and will rollback if a concurrent transaction modifies the same data. Oracle works mostly the same way. MySQL uses row and table locking which works most of the time, but will occasionally rollback a transaction to avoid a deadlock.

In any case, the above should make clear why it's standard practice for an application to expect that a transaction may fail due to concurrency issues, and be prepared to retry it in that case. However, that seems to be a direct odds with the way CI handles database access.

The first problem is that if db_debug is enabled, CI bails out at the first sign of trouble and exits with an error message. There doesn't appear to be any way for the application designer to say, "I know this particular statement might fail, override automatic error handling just for it." So the solution seems to be to set db_debug = FALSE. Okay, I can deal with that, even if it's mildly annoying. No automatic emails to the admin on real database errors unless I want to manually check every single query.

The second problem is the trans_strict setting, which defaults to on. When that is enabled, should a transaction fail, it sets a flag. Once that flag is set, any future transactions are silently rolled back by CodeIgniter at the end, regardless of success or failure. Auto-committed single statements are unaffected by this and apply just fine, possibly leading to inconsistent data hitting the database.

Again, maybe I'm missing something, but I cannot possibly imagine a scenario where that makes any sense. Transactions are all about isolation, why should they be impacted by a completely unrelated transaction that happened to fail earlier in the request cycle? Especially since transactions are primarily used when you're expecting something to go wrong and want to roll back a group of statements at once. I would think that anyone bothering to use the transaction features is also checking whether they succeeded or not and handling it appropriately, otherwise there would be no point.

So yes, I can set db_debug = FALSE and lose some functionality, cause worry to maintainers when they see me calling trans_strict(FALSE) everywhere, and get close to what I want. However that seems like a long way to go for basic functionality, and maybe I'm crazy and there is some common use case to justify it working the way it does. Smile

So how about it? Is anyone using CodeIgniter's transaction support for anything, and have you run into these issues?
#2

[eluser]Kyle Johnson[/eluser]
The way I've always understood transactions is to think of a banking transaction.

BEGIN TRANSACTION
Deduct $500 from my account
Add $500 to some other account

If either of the above failed, ROLLBACK the entire transaction
If they both succeed, COMMIT the transaction.

Essentially, transactions mean... exactly that. Treat this entire batch of statements as one transaction, they must all succeed for the transaction to succeed, and if any should fail, we rollback all of them.

A query is "successful" as long as it returns from the database without any errors, so an empty set may be a successful query, but perhaps that set should of had 1 row. This gives you the chance to say (in your code), "Hey, this should have 1 row, let's not commit this transaction because something is wrong."

Does that help at all?

-Kyle




Theme © iAndrew 2016 - Forum software by © MyBB