The Scope Of SERIALIZABLE Transaction Row-Locking Is Larger When Rows Don't Yet Exist In MySQL 5.7.32
After looking at using SERIALIZABLE transactions to enforce unique-naming constraints in a MySQL and Lucee CFML application, I posted a follow-up exploration on the scope of SERIALIZABLE transaction row-locking. What I didn't understand at the time of that follow-up post, however, is that the scope of the row-locking changes significantly if a portion of the index is empty. I believe this scope-change is related to a MySQL "bug". And while I don't fully understand what is being discussed in that MySQL bugbase, I thought it would be worth demonstrating the difference in row-locking based on the state of the database in MySQL 5.7.32 and Lucee CFML 5.3.7.47.
Just as with the previous exploration, I'm going to use a simple widget table that contains an index that has a prefix on userID:
CREATE TABLE `widget` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userID` int(10) unsigned NOT NULL,
`name` varchar(100) NOT NULL,
`createdAt` datetime NOT NULL,
`updatedAt` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `IX_byUser` (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The IX_byUser index should allow our row-level transaction locking to be localized to the set of rows associated with a given userID (as I demonstrated in my previous post). However, if there are no rows yet created with a given userID, this fact is no longer so truthy.
To demonstrate this, we're going to have two CFThread tags compete to create a new row, each using a different userID value. At the top of the demo, however, we're going to TRUNCATE the widget table such that neither user has any existing rows within their slice of the index.
In the following code, each CFThread tag has the exact same logic - each enters into a transaction and then performs a sleep() in order to ensure that both CFThread tags have time to enter into overlapping transactions (and overlapping read-locks):
<cfscript>
include "./db-functions.cfm";
// The widget table has an index that starts with "userID", which should mean that
// serializable transactions are localized to a given userID. We're going to test
// this using the following two users, setup to race for new records.
sarahID = 1;
johnID = 2;
// CAUTION: When applying a serializable transaction to a section of the index that
// is currently empty (ex, no widgets yet created for a given userID), the scope of
// the locking appears to be farther reaching and can cause DEADLOCKS across
// different sections (userIDs) of the index.
// --
// READ MORE: https://bugs.mysql.com/bug.php?id=25847
// --
truncateWidgets();
// createWidget( sarahID, "Sarah's First Widget" );
// createWidget( johnID, "John's First Widget" );
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
thread
name = "sarahThread"
userID = sarahID
widgetName = "Sarah's Widget #randRange( 1, 9999 )#"
{
transaction isolation = "serializable" {
// MySQL is going to LOCK the index range scanned during this SERIALIZABLE
// transaction, which in our case - with an index on `userID` - is going to
// be all of the rows that are owned by the given user.
if ( isWidgetWithNameExists( userID, widgetName ) ) {
throw( type = "NameAlreadyExists" );
}
// NOTE: Using sleep() here to ensure that both CFThread tags enter into an
// overlapping transaction. This means they will have both LOCKED READ ROWS
// at this point in the control flow.
sleep( 500 );
createWidget( userID, widgetName );
}
}
thread
name = "johnThread"
userID = johnID
widgetName = "John's Widget #randRange( 1, 9999 )#"
{
transaction isolation = "serializable" {
// MySQL is going to LOCK the index range scanned during this SERIALIZABLE
// transaction, which in our case - with an index on `userID` - is going to
// be all of the rows that are owned by the given user.
if ( isWidgetWithNameExists( userID, widgetName ) ) {
throw( type = "NameAlreadyExists" );
}
// NOTE: Using sleep() here to ensure that both CFThread tags enter into an
// overlapping transaction. This means they will have both LOCKED READ ROWS
// at this point in the control flow.
sleep( 500 );
createWidget( userID, widgetName );
}
}
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
// Wait for both competing threads to re-join the parent thread.
thread action = "join";
dump(
label = "Sarah's Widgets",
var = getWidgetsByUserID( sarahID ),
metainfo = false
);
dump(
label = "John's Widgets",
var = getWidgetsByUserID( johnID ),
metainfo = false
);
dump(
label = "CFThreads",
var = cfthread
);
</cfscript>
Note that I have two createWidget() calls commented-out at the top - we'll come back to this in a moment. But, for now, if we run this ColdFusion code, we invariably end-up creating a new row in one of the threads while the other thread errors-out with the following MySQL exception:
Deadlock found when trying to get lock; try restarting transaction
Usually this error occurs in the second thread; but, sometimes it occurs in the first thread depending on which thread is spawned first. And, no matter how many times I refresh the page - which calls a TRUNCATE on the table - we always end up with a deadlock error.
Always.
Now, let's go back into that code an uncomment those two createWidget() calls:
<cfscript>
// ... more code ...
// CAUTION: When applying a serializable transaction to a section of the index that
// is currently empty (ex, no widgets yet created for a given userID), the scope of
// the locking appears to be farther reaching and can cause DEADLOCKS across
// different sections (userIDs) of the index.
// --
// READ MORE: https://bugs.mysql.com/bug.php?id=25847
// --
truncateWidgets();
createWidget( sarahID, "Sarah's First Widget" );
createWidget( johnID, "John's First Widget" );
// ... more code ...
</cfscript>
All this is doing is ensuring that at least one record exists in each slice of our index before we spawn our competing CFThread tags. And, when we do this, our ColdFusion code executes with no problem!
Where things get a little confusing for me is when one of the userID values has a row but the other one does not. So, if we go back and run this code:
<cfscript>
// ... more code ...
truncateWidgets();
createWidget( sarahID, "Sarah's First Widget" );
// createWidget( johnID, "John's First Widget" );
// ... more code ...
</cfscript>
... we invariably get a deadlock. No matter how many times I refresh the page.
But, if then run this version of the code:
<cfscript>
// ... more code ...
truncateWidgets();
// createWidget( sarahID, "Sarah's First Widget" );
createWidget( johnID, "John's First Widget" );
// ... more code ...
</cfscript>
... it always succeeds. No matter how many times I refresh the page.
I believe this has to do with the fact that the sarahID value is less than the johnID value; which likely relates to the MySQL forum thread on locking something called supermum. In fact, if we change sarahID=11 - such that it is greater than the johnID - and then re-run the last version of the code, we will get a deadlock.
I don't fully understand what is actually happening here - these low-level database mechanics are over my head. And, I don't immediately think that this information will actually change my database transaction strategies. But, it is good to know that I may see "unexpected" transaction deadlocks within an index-design that should prevent them.
Want to use code from this post? Check out the license.
Reader Comments
@All,
The reason that I ran into the scoping issue in this post was because I was in the middle of a code kata in which I was trying to implement a group-based incrementing value in MySQL and ColdFusion:
www.bennadel.com/blog/4135-creating-a-group-based-incrementing-value-in-mysql-5-7-32-and-lucee-cfml-5-3-7-47.htm
This ended up being a really fun exploration!