MySQL locking with INSERT/DELETE workload

hingo's picture

Yep. I see this too at work. InnoDB is in my opinion really good at handling concurrent workloads. So good I was surprised when I eventually found a project that was having locking issues. SHOW ENGINE INNODB STATUS showed queries had been waiting for hours on some locks they would never get. Yeah, it's a large and busy database, but it took me by surprise nevertheless.

It turns out, while InnoDB handles concurrent UPDATEs very efficiently, a combination of transactions that DELETE and INSERT rows - even just in the same general area of a table - will make the transactions wait for each other. Hence a workload that does a lot of inserts and deletes may get you into trouble. The solution is to change to READ COMMITTED or even READ UNCOMMITTED mode.

Thanks to Aaron from Ideeli for writing a proper blog about this, so I don't have to :-)

Update: Tagged Drizzle. Since this is an InnoDB issue, it of course applies just the same for Drizzle users too.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
MySQL locking with INSERT/DELETE workload | MySQL | Syngu's picture

Pingback

[...] Yep. I see this too at work. InnoDB is in my opinion really good at handling concurrent workloads. So good I was surprised when I eventually found a project that was having locking issues. SHOW ENGINE INNODB STATUS showed queries had been waiting for hours on some locks they would never get. Yeah, it's a large and busy database, but it took me by surprise nevertheless. It turns out, while InnoDB handles concurrent UPDATEs very efficiently, a combination of transactions that DELETE and INSERT rows - even just in the same general area of a table - will make the transactions wait for each other. Hence a workload that does a lot of inserts and deletes may get you into trouble. The solution is to change to READ COMMITTED or even READ UNCOMMITTED mode. read more    MySQL Read the original post on Planet MySQL... [...]

 Log Buffer #262, A Carnival of the Vanities for DBAs | The 's picture

Pingback

[...] Henrik Ingo discusses MySQL locking with INSERT/DELETE workload. [...]

Gavin Towey's picture

TX Isolaton

Given that the default transaction isolation mode is READ COMMITTED, what level were you running at before you switched?

hingo's picture

MySQL default is REPEATABLE

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Use [fn]...[/fn] (or <fn>...</fn>) to insert automatically numbered footnotes.
  • Allowed HTML tags: <h1> <h2> <h3> <h4> <p> <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <br> <sup> <div> <blockquote> <pre> <img>
  • Lines and paragraphs break automatically.
  • Web page addresses and e-mail addresses turn into links automatically. (Better URL filter.)

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
3 + 6 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.