Is there a more efficient method than transactions?

Question

insert into table1 ...;
update table2 set count=count+1;

The above inserts something into table1, and if it succeeds, updates the count field of table2.

Of course this kind of thing can be handled by transactions, but transactions need to lock the table, which will be not efficient in a high concurrent system. And it can be even worse if you need to update multiple tables in that transaction.

What's your solution?

I'm using PHP, and I implement transactions this way:

mysql_query('begin');

mysql_query($statement1);

mysql_query($statement2);
...
mysql_query('commit');

So it looks like all tables referred in those $statement will be locked?

Answer 1

these looks more like a "Triggers" job to me. onInsert do something.

Answer 2

A transaction (which in context of MySQL assumes InnoDB) will not need to lock the whole table.

The INSERT will lock the individual row without gap locks.

The UPDATE will not lock any gaps too if you provide an equality or IN condition on an indexed field in the WHERE clause.

This means that with a properly indexed table, INSERTs will not block each other, while the UPDATEs will only block each other if they affect the same row.

The UPDATE will of course lock the individual row it affects, but since it is the last operation in your transaction, the lock will be lifted immediately after the operation is commited.

The locking itself is in fact required so that two concurrent updates will increment the counts sequentially.

Answer 3

Use InnoDB storage engine. It is row level locking instead of MyISAM which is table level locking.

Answer 4

Transactions are great to ensure a "all or nothing" behavior -- even if there is a high load or high concurrency in your system, you shouldn't stop using transactions, at least if you need your data to stay coherent !

(And transactions will not necessarily lock the whole table(s))

Answer 5

Transactions will not necessarily request a lock for the whole table.

In addition, InnoDB supports various translation isolation levels using different locking strategies. You may want to check:

• MySQL: SET TRANSACTION Syntax
• MySQL Transactions, Part II - Transaction Isolation Levels

Answer 6

If speed were of the absolute essence, I might be tempted to cache the update count in memory (using thread-safe code) and only write it back to the database periodically. I would certainly use a transaction when I did so. The downside to this is that you would need to be willing to treat the count as only approximate. If you needed it to be absolutely correct for an operation, then it would need to be recalculated, again within a transaction.

As others have pointed out, using a database that supports row-level locking and simply using an explicit (or implicit via a trigger) transaction is probably easier. It's definitely more accurate and safer.