Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question
2  
You should take care of this yourself. WP doesn't use MySQL transactions, so it doesn't handle this nicely –  onetrickpony Dec 22 '11 at 1:22
add comment

1 Answer 1

up vote 8 down vote accepted

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:

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.

share|improve this answer
    
@RolandoMySQLDBA thanks a lot! That does help me get a clearer picture, but raises a few more questions.. will be back later with clearer questions :) On an unrelated note, what is the standard for accepting an answer on StackOverflow? Do I wait for more answers? Can I change my accepted answer if someone submits a better answer later? –  Samudra Dec 23 '11 at 18:47
    
Yes, you check the answer you want. If you see a better answer, uncheck it and check the better answer. –  RolandoMySQLDBA Dec 23 '11 at 19:01
    
You can easily wait for better answers in the meantime until you are ready to accept. –  RolandoMySQLDBA Dec 23 '11 at 19:09
    
@RolandoMySQLDBA thanks! I am new :) –  Samudra Dec 23 '11 at 22:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.