Strictly from a MySQL Point-of-View
SHORT VERSION
- Table locks
- Writes are first come, first serve
- Reads slow down writes from initiating
- 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.