MySQL locking with INSERT/DELETE workload

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.

Gavin Towey (not verified)

Wed, 2012-03-14 00:27

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

Add new comment

The content of this field is kept private and will not be shown publicly. Cookie & Privacy Policy
  • No HTML tags allowed.
  • External and mailto links in content links have an icon.
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.
  • Use [fn]...[/fn] (or <fn>...</fn>) to insert automatically numbered footnotes.
  • Each email address will be obfuscated in a human readable fashion or, if JavaScript is enabled, replaced with a spam resistent clickable link. Email addresses will get the default web form unless specified. If replacement text (a persons name) is required a webform is also required. Separate each part with the "|" pipe symbol. Replace spaces in names with "_".
About the bookAbout this siteAcademicAmazonBeginnersBooksBuildBotBusiness modelsbzrCassandraCloudcloud computingclsCommunitycommunityleadershipsummitConsistencycoodiaryCopyrightCreative CommonscssDatabasesdataminingDatastaxDevOpsDrizzleDrupalEconomyelectronEthicsEurovisionFacebookFrosconFunnyGaleraGISgithubGnomeGovernanceHandlerSocketHigh AvailabilityimpressionistimpressjsInkscapeInternetJavaScriptjsonKDEKubuntuLicensingLinuxMaidanMaker cultureMariaDBmarkdownMEAN stackMepSQLMicrosoftMobileMongoDBMontyProgramMusicMySQLMySQL ClusterNerdsNodeNoSQLodbaOpen ContentOpen SourceOpenSQLCampOracleOSConPAMPPatentsPerconaperformancePersonalPhilosophyPHPPiratesPlanetDrupalPoliticsPostgreSQLPresalespresentationsPress releasesProgrammingRed HatReplicationSeveralninesSillySkySQLSolonStartupsSunSybaseSymbiansysbenchtalksTechnicalTechnologyThe making ofTungstenTwitterUbuntuvolcanoWeb2.0WikipediaWork from HomexmlYouTube