MySQL transactions

[Dani]

I need help with implementing MySQL transactions with PHP. Specifically, I'm confused between the flags WITH CONSISTENT SNAPSHOT, READ WRITE, and READ ONLY, what their differences are, and how they relate to table locking. I already read the MySQL reference manual but I'm still confused.

[Dani]

So the behavior I'm experiencing, and I'm sure those of you who have been active on DaniWeb the past few days have been experiencing, is INSERT queries are taking a realllllly long time (often 15+ seconds). Starting new threads, replying to posts, ... it's all taking a really long time.

I went ahead and disabled transactions (I was just using START TRANSACTION with no flags), and the problem immediately went away. What flag do I need to use to make it non-blocking?

[Dani]

Not sure if it matters, but I was using CodeIgniter's trans_start() and trans_complete() functions instead of implementing transactions natively with MySQLi.

Also, I'd like to point out that I don't want to block any reads, and ideally I wouldn't want to block multiple inserts to the same table at the same time across different transactions. All I'm trying to preserve is that if a single transaction is supposed to make two inserts, but the script dies before the second one completes, that the first one gets rolled back.

[pty]

All RDBMs have more-or-less the same Transaction Isolation Levels but sometimes they go by slightly different names and MySQL has a few extra oddities. Before going further understanding what a dirty read is and how to avoid it is important.

READ WRITE and READ ONLY set the level of access that transaction has. If you use READ WRITE you can both read from and write to tables inside your transaction. With READ ONLY, you can't modify data. That bit's easy.

WITH CONSISTENT SNAPSHOT is a tiny bit trickier, but essentially when you open the transaction all data used inside the query will be as if it was read when the snapshot was created (either at the start of the transaction or at the point it's first read), rather than how it currently is. From the docs,

With REPEATABLE READ isolation level, the snapshot is based on the time when the first read operation is performed. With READ COMMITTED isolation level, the snapshot is reset to the time of each consistent read operation.

So, it comes back to transaction isolation levels. READ COMMITTED just means that any data read has beencommitted to the database. If you select a value from a table at the beginning of your transaction, then update it (and commit) in concurrently in another transaction, then finally select it at the end of your first transaction, the value will be the new value.

REPEATABLE READ means that once you've selected a value inside a transaction, it will remain the same independent of what's going on in the database.

So, this whole area can be quite confusing, but it doesn't really answer why your insert statement is slow. What exactly is your transaction doing? Is it just

begin transaction
    insert into ....
    update  ....
commit transaction;

Or are you doing other things too? There could be a number of things making it take so long - updating indexes perhaps. If you're just inserting comments, perhaps relaxing your transaction's isolation level to READ COMMITTED may speed things up? Difficult to say without knowing the bottleneck though.

[pty]

Did lowering the transaction isolation level to READ COMMITTED make any difference to the timing?

[Dani]

Please don't be disappointed in me.

What I was doing, out of simplicity's sake, was starting a transaction in the constructor of the database class, and committing the transaction in the destructor of the database class, for all POST requests. (POST requests are less than 1% of all web requests.) Could that have been the problem??

[Dani]

Also, it looks like READ COMMITTED likes to lock things. I need to do as little locking as possible, otherwise the server will crash pretty quickly.