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

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.