How I fell in love with MySQL ? Updates VS Inserts

Well in 2011, for a week we would see disruptions in our ERP servers especially around 7pm.

  • We had a ‘job’ table & multiple app servers(cluster) inserting jobs and also picking them up.

  • At around 7pm , we would notice that the number of Pending jobs would increase dramatically i.e. app servers would not execute them.

  • The ‘confirm shipment’ job – a critical job for us would not be executed – leaving the fate of many shipments hanging in limbo.

I did do a lot of debugging around this – looking for clues – the major one was found in

show engine innodb status

TRX HAS BEEN WAITING 120 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 560985 n bits 72 index `status_idx` of table `test`.`job_sandbox` trx id 0 9133886 lock_mode X locks rec but not gap waiting

It was a fight between Inserts & Updates on an index.

The sequence of events are available in this bug i filed, which was marked invalid – but none the less, it was an important lesson –

“Updates can get stuck because of inserts”

The solution for the above issue mentioned in the bug is as follows:

Old Txn:

update t7_ibu set status='service_queued' where status='service_pending' ;

New Txn:

//convert update to select.
//first get all the primary key id's
select id from t7_ibu where status='service_pending' ;
//then update each row using primary key value
foreach (primary_key in result set){
update t7_ibu set status='service_queued' where id = primary_key;
}

Since the update is confined to a particular row – the index lock can be granted to both the insert and the update. Also please note: multiple servers exist – they all do the same thing i.e. insert job and update job.

And this is how i fell in love with MySQL, this issue did it 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s