S h o r t S t o r i e s

// Tales from software development

Avoiding table locking in MySQL

leave a comment »

As a follow on to the previous post, I needed to change a DATETIME column in a table to a BIGINT column and to convert all the DATETIME values to .NET DateTime serialized binary data.

I wrote a small C# program to execute a SQL SELECT statement to return all the rows in the table, read the DATETIME column values, and then execute a SQL UPDATE command to write the DateTime ToBinary() value into the BIGINT column.

I tested the program successfully on a small test database with a few hundred rows in the table but when I tried to execute it against the live database that had 70,000+ rows in the table it failed when the first UPDATE command timed out.

I guessed it was a locking issue and looked up the syntax of the MySQL SELECT statement. There are locking hints that allow you to modify the locking that MySQL performs but not to turn locking off.

The solution was to use the SQL_BUFFER_RESULT keyword available on the SELECT statement:

SELECT SQL_BUFFER_RESULT <column-list> FROM <table>;

This ensures that the query results are copied to temporary storage and the table is locked for the minimum time possible.

The program ran successfully after this was added to the SELECT statement.

Advertisements

Written by Sea Monkey

April 5, 2011 at 8:00 pm

Posted in Development

Tagged with , ,

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

%d bloggers like this: