How does Wordpress handle MySQL row lock errors?

Question

If there is a WordPress plugin which updates rows in a custom table, but that update encounters a row lock, what happens? E.g. two users simultaneously fire an update on TABLE1, one setting AGE=1 and the other setting AGE=2:

• Will there be a row lock at all?
• Will MySQL handle this "gracefully" and one of the 2 updates simply be "lost"?
• Does Wordpress handle it gracefully?
• Do I need to take care of it in the plugin?

The data is not very important, as in I dont need to track every change. But I do need to handle any errors that might arise.

Answer 1

Strictly from a MySQL Point-of-View

SHORT VERSION

MyISAM Storage Engine

• Table locks
• Writes are first come, first serve
• Reads slow down writes from initiating

InnoDB Storage Engine

• Row locks
• Transactions (non blocking)
• Deadlock may occur when updating indexes

LONG VERSION

If the underlying tables use the MyISAM Storage Engine, row locks are not possible. Each DML statement (INSERT,UPDATE, and DELETE) causes a full table lock.

If 50 DB Connections attempt to update onw row in mydb.mytable

• 1 table lock, 1 update, 49 DB Connections wait
• 1 table lock, 1 update, 48 DB Connections wait
• 1 table lock, 1 update, 47 DB Connections wait
• ...
• 1 table lock, 1 update, 02 DB Connections wait
• 1 table lock, 1 update, 01 DB Connections waits

Get the picture? You can see where a bottleneck can occur on just one table. Now, imagine a heavily-trafficked WordPress site. SELECTs get priority over DML Statements (The exception would be concurrent INSERTs on MyISAM tables, provided the MyISAM table has only SELECTs and INSERTs done with no gaps in between). Hundreds, or even dozens, of SELECTs in between DML Statements can slow down the aformentioned bottleneck even more.

The saving grace of MyISAM in a heavily trafficked WordPress site is that deadlocks can never occur.

If the underlying tables use the InnoDB Storage Engine, row locks (even on the same row) can never block reads, but deadlocks are still possible during writes. With AUTOCOMMIT=0 set in /etc/my.cnf, each DML statement (INSERT,UPDATE, and DELETE) will be executed as a single line transaction. Individual row locks are issued. Thus, 50 DB Connections can go after 50 different rows and nothing tragic happens.

Where can deadlocks come in?

Since the PRIMARY KEY of InnoDB tables is contained within the Clustered Index (internally known as the gen_clust_index), the row data is tightly coupled with the index entries. Any index made against columns not part of the PRIMARY KEY are cataloged with two basic items, 1) the column value, and 2) the gen_clust_index key. At times, updating indexed columns in InnoDB may cause what I jokingly call index constipation. That's occurs when two or more locks are generated on index entries stored close to one another. This is possible in a heavily trafficked website.

I once helped a Developer see why this can happen in the DBA StackExchange. That developer made code changes afterwards. Here were those posts:

• Will these two queries result in a deadlock if executed in sequence?
• Trouble deciphering a deadlock in an innodb status log
• Reasons for occasionally slow queries?

YOUR QUESTION

One of the updates will be lost regardless of the Storage Engine. Only the last UPDATE on a column sets the final value.

CONCLUSION

This post does not favor either Storage Engine. These are simply the facts about what can and will happen when writing to tables.