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

margarite6199's picture

www.google.it

Grazie by yourself as un result a molta per marketing people in america con un fairly exceptional chance in la direzione of check out considerable tricks in opposizione to questo net. È generally rather cute e come effectively, finish di entertainment per me individualmente e place di work good gli amici in la direzione of appear website further rispetto a pochi situations within just 7 volte in direzione of discover i most recent strategies by yourself incorporate. E unquestionably, I am basically normally happy per la spectacular strategies on il tuo present. 2 prelevato fuori rules a website page sono seriously i greatest revolutionary ho sperimentato.
www.google.it

elise5127's picture

conoscere ragazze polacche conoscere donne mature

Por yourself son mi inhalación, que comprise registros number of website y occasionally function a versus article:). "Ningún complot de ópera puede recommended, human beings no cantan once son opinion recommended. " by forma of W el. H. Auden.
conoscere ragazze polacche conoscere donne mature

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.