Using SELECT FOR UPDATE To Synchronize MySQL Row Access In ColdFusion
At work, when calling CockroachDB, I noticed that Claude Code was using a SELECT ... FOR UDPATE construct in order to manage row access within a multi-step transaction workflow. Row locking is a concept many databases provide; and I've somehow made it this far into my career without knowing that this database concept exists in any consumable way. MySQL, which I use in my personal projects, exposes the same mechanics when using the InnoDB storage engine; and it can be used to apply both exclusive and readonly locks at the table row level. Since Big Sexy Poems is my current coding playground, I wanted to start applying this new-found technique to my Adobe ColdFusion 2025 code.
Big Sexy Poems is a very low volume application. In fact, at the time of this writing, I believe I'm the only user. As such, concerns about locking and concurrency are almost nonexistent. That said, I want to build up a foundational level of understanding and correctness as a learning experience that I can pull-forward into other higher-volume applications (like this blog).
MySQL Locking Reads
When using the InnoDB storage engine, MySQL offers two types of locking reads that synchronize access to rows within a table.
SELECT ... FOR SHARE. This creates a shared ("read only") lock on the rows that are read from the database. When a shared lock is in place, other queries can read the same rows and even acquire shared locks without blocking; but, they cannot modify the rows or acquire exclusive locks until the shared lock is released.
Mental model: I'm reading this row and I need to prevent anyone else from modifying it for the lifetime of the current
transactionblock.
SELECT ... FOR UPDATE. This creates an exclusive lock on the rows that are read from the database. When an exclusive lock is in place, no other queries can acquire a lock (shared or exclusive) or modify the same rows until the exclusive lock is released.
Mental model: I'm reading this row and I intend to write back to either this row or a child row that depends upon its current state. The locked row acts as a choke-point for workflow synchronization.
Locking reads affect other locking reads and row mutations, but not ordinary read traffic. Any SELECT statement that runs outside of a transaction — or inside of a transaction block but without FOR SHARE / FOR UPDATE mechanics — can read any row, regardless of whether or not the row is locked. In other words, general read-only SQL concurrency is not affected by locking.
UPDATE and DELETE statements, on the other hand, always acquire an exclusive lock on the target row, which will wait for any incompatible lock held by another transaction. As such, any row that is explicitly locked by FOR SHARE or FOR UPDATE will implicitly block any and all UPDATE and DELETE statements, regardless of how those statements are being executed.
INSERT statements are only blocked by concurrent locks if those concurrent locks are using "gap locking". Gap locking doesn't lock specific rows; instead it locks a range of rows. Gap locking is triggered when a locking read does anything other than equality-match on a single existing row in a unique index. For example, in a parent-child relationship, performing a locking read on a table WHERE parentID = ?, will lock concurrent INSERT statements of any new row with the same parentID (the database is locking the range of rows with the same parentID condition).
Note: some of the assertions above are affected by the isolation level of your
transactionblock. But, I don't have a strong enough understanding to explain the finer details of how isolation level affects things like non-locking reads. I will leave that as a research exercise for the reader.
Adding Locking Mechanics To My Data Access Layer
In Big Sexy Poems, my low-level "model" components all follow the same pattern. Each model has a *Model.cfc and a *Gateway.cfc ColdFusion component. The Gateway components abstract away the verbose SQL syntax allowing the Model components to deal with the higher-level control flow, data validation, and results handling.
The ability to perform a locking read has to start at the Gateway level and then be exposed up through the call stack. Each "read" method in my Gateways now accepts a withLock argument that conditionally includes the FOR SHARE ("read only") or FOR UPDATE ("exclusive") clause at the end of the SQL statement.
To illustrate, here's a truncated version of my PoemGateway.cfc. Note that I still use tag-based *Gateway.cfc components so that I can use the cfquery and cfqueryparam tags, which offer far superior ergonomics when compared to the string-based queryExecute() method:
<cfcomponent extends="core.lib.model.BaseGateway">
<cffunction name="getByFilter" returnType="array">
<cfargument name="id" type="numeric" required="false" />
<cfargument name="userID" type="numeric" required="false" />
<cfargument name="collectionID" type="numeric" required="false" />
<cfargument name="withSort" type="string" required="false" default="id" />
<cfargument name="withLock" type="string" required="false" default="" />
<cfset assertIndexPrefix( arguments ) />
<cfquery name="local.results" result="local.metaResults" returnType="array">
SELECT
id,
userID,
collectionID,
name,
content,
createdAt,
updatedAt
FROM
poem
WHERE
TRUE
<cfif ! isNull( id )>
AND
id = <cfqueryparam value="#id#" cfsqltype="bigint" />
</cfif>
<cfif ! isNull( userID )>
AND
userID = <cfqueryparam value="#userID#" cfsqltype="bigint" />
</cfif>
<cfif ! isNull( collectionID )>
AND
collectionID = <cfqueryparam value="#collectionID#" cfsqltype="bigint" />
</cfif>
ORDER BY
<cfswitch expression="#withSort#">
<cfcase value="name">
name ASC,
id ASC
</cfcase>
<cfdefaultcase>
id ASC
</cfdefaultcase>
</cfswitch>
<cfswitch expression="#withLock#">
<cfcase value="readonly">
FOR SHARE
</cfcase>
<cfcase value="exclusive">
FOR UPDATE
</cfcase>
</cfswitch>
</cfquery>
<cfreturn results />
</cffunction>
</cfcomponent>
By default, the withLock argument is empty and applies no locking semantics to the generated SELECT SQL statement. A "readonly" argument is translated to the FOR SHARE clause and an "exclusive" argument is translated to the FOR UPATE clause. I used these terms because they align with CFML's existing CFLock tag semantics. And, because they feel less like the SQL implementation is leaking out of its abstraction and into the calling context.
This withLock argument is then pulled-up through the *Model.cfc component layer. To illustrate, here's a truncated version of my PoemModel.cfc. Note that the withLock argument is exposed in both the get() and getByFilter() methods:
component {
// Define properties for dependency-injection.
property name="gateway" ioc:type="core.lib.model.poem.PoemGateway";
property name="validation" ioc:type="core.lib.model.poem.PoemValidation";
// ColdFusion language extensions (global functions).
include "/core/cfmlx.cfm";
// ---
// PUBLIC METHODS.
// ---
/**
* I get a model.
*/
public struct function get(
required numeric id,
string withLock
) {
var results = getByFilter( argumentCollection = arguments );
if ( ! results.len() ) {
validation.throwNotFoundError();
}
return results.first();
}
/**
* I get the model that match the given filters.
*/
public array function getByFilter(
numeric id,
numeric userID,
numeric collectionID,
string withSort,
string withLock
) {
return gateway.getByFilter( argumentCollection = arguments );
}
}
By default, the Model layer sets the withLock argument to null, which means that, by default, the underlying Gateway layer omits the locking semantics. But any withLock argument value ("readonly" or "exclusive") send to the Model layer is implicitly passed-down to the Gateway layer using the argumentCollection construct.
I don't bother validating the withLock value since it isn't a user-provided value. But, adding validation in the future is something I might do for completeness.
Services As The Transaction Orchestration Layer
These small tweaks to the Model and Gateway components allow my entity data to be read with optional locking reads. And, as a rule of thumb, I've designated my service layer — those components that use the *Service.cfc naming convention — as the place where locking decisions are made.
My Service layer is the "application core". It's the layer that sits in between the "Router" / "Controller" and the "Model" and implements the vast majority of the business logic. It understands how the entities all relate, what the intent of each invocation is, and how locking therefore needs to be applied in order to maintain data integrity.
For example, when a poem is created, the locking doesn't have to be overwrought. All I have to do is lock the foreign keys using FOR SHARE in order to make sure at they aren't deleted by concurrent requests. As such, the user (owner) and the collection (categorization) are "readonly" locked before the INSERT is executed:
component {
/**
* I create a new poem.
*/
public numeric function create(
required struct authContext,
required numeric userID,
required numeric collectionID,
required string name,
required string content
) {
var context = poemAccess.getContextForParent( authContext, userID, "canCreateAny" );
var user = context.user;
testCollectionID( authContext, userID, collectionID );
transaction {
var userWithLock = userModel.get(
id = user.id,
withLock = "readonly"
);
if ( collectionID ) {
var collectionWithLock = collectionModel.get(
id = collectionID,
withLock = "readonly"
);
}
var poemID = poemModel.create(
userID = userWithLock.id,
collectionID = collectionID,
name = name,
content = content,
createdAt = utcNow()
);
// Snapshot the persisted poem as the initial revision. Since we created this
// poem inside a transaction, we don't have to lock the repeatable read - no
// other request can see this poem until it's been committed.
var poem = poemModel.get( poemID );
revisionModel.create(
poemID = poem.id,
name = poem.name,
content = poem.content,
createdAt = poem.updatedAt
);
} // End: transaction.
return poemID;
}
}
In this create() call, there are a few things to note:
I'm performing my access control check outside of the transaction. My call to
poemAccessreads a lot of the same data; but the intent of the call is different. ThepoemAccessdetermines if the current request is "allowed in the building" and can "perform the desired action". Once that check is complete, I consider the matter of permissions done; and I move onto the fulfillment of the service method.Because the access check and the service fulfillment are different aspects of the request processing, my
transactionblock re-reads some of the same data (the user / owner of the poem in this case); but it does so with locking read semantics. This may seem like unnecessary overhead. But between connection pooling and in-memory row caches provided by the database, the overhead is all but non-existent and the repeated read provides a cleaner separation of concerns.
Both of my locking reads use "readonly" since all I'm doing when creating a poem is making sure that the foreign keys remain stable for the rest of the transaction. Updating a poem, on the other hand, gets a bit more intense. Not only do I have to acquire an "exclusive" lock on the poem record since I'm updating it, the "exclusive" lock is doing double-duty as the synchronization point in the revision workflow.
In this case, you can think of the poem as an "aggregate root" (in Domain Driven Design parlance). The FOR UPDATE lock that I'm applying to the poem record is intend to both lock the poem row but also to act as a mutex on all of the revision rows that pertain to the poem. This concept is upheld by the fact that other revision mutation workflows also attempt to lock the parent poem; but cannot until this exclusive lock is released.
component {
/**
* I update the given poem.
*/
public void function update(
required struct authContext,
required numeric id,
numeric collectionID,
string name,
string content
) {
var context = poemAccess.getContext( authContext, id, "canUpdate" );
var poem = context.poem;
if ( ! isNull( collectionID ) ) {
testCollectionID( authContext, poem.userID, collectionID );
}
transaction {
if ( ! isNull( collectionID ) ) {
var collectionWithLock = collectionModel.get(
id = collectionID,
withLock = "readonly"
);
}
var poemWithLock = poemModel.get(
id = poem.id,
withLock = "exclusive"
);
poemModel.update(
id = poemWithLock.id,
collectionID = arguments?.collectionID,
name = arguments?.name,
content = arguments?.content,
updatedAt = utcNow()
);
// We only need to worry about a revision if the poem content has changed.
// --
// Note: revision are tied to the content. As such, even if the collection ID
// was changed above, a revision will only ever be created if the content of
// the poem meaningfully changed.
if (
( isNull( name ) || ! compare( name, poemWithLock.name ) ) &&
( isNull( content ) || ! compare( content, poemWithLock.content ) )
) {
return;
}
// We have to re-read the persisted poem for the revision snapshot; but the
// row-lock already exists, so we don't need to lock the row - we just need to
// make sure that we're reading the updated row cache so that our poem changes
// make it into the revision.
var updatedPoem = poemModel.get( poemWithLock.id );
var windowInSeconds = 120;
var cutoffAt = updatedPoem.updatedAt.add( "s", -windowInSeconds );
var maybeLastRevision = revisionModel.maybeGetMostRecentByPoemID( updatedPoem.id );
// If there's no previous revision, or the window has closed, create a new one.
if (
! maybeLastRevision.exists ||
( maybeLastRevision.value.updatedAt <= cutoffAt )
) {
revisionModel.create(
poemID = updatedPoem.id,
name = updatedPoem.name,
content = updatedPoem.content,
createdAt = updatedPoem.updatedAt
);
// Otherwise, update the existing revision within the window.
} else {
revisionModel.update(
id = maybeLastRevision.value.id,
name = updatedPoem.name,
content = updatedPoem.content,
updatedAt = updatedPoem.updatedAt
);
}
} // End: transaction.
}
}
Database row locking, like any other kind of locking, doesn't work in isolation — it works because other parts of the same application agree to an implicit contract that rows are always locked in meaningful ways. When I say that the "exclusive" lock on the poem, in the above workflow, acts as a mutex for revisions, this is only true because other revision mutations workflows agree to lock the same poem row.
For example, when we delete a revision, we make sure to lock the parent poem using a "readonly" (FOR SHARE) lock that will be incompatible with any existing "exclusive" lock on the same row. Note that the RevisionCascade.cfc encapsulates the logic required to traverse the entity tree, unlinking and deleting nested associations.
component {
/**
* I delete a revision.
*/
public void function delete(
required struct authContext,
required numeric id
) {
var context = revisionAccess.getContext( authContext, id, "canDelete" );
var user = context.user;
var poem = context.poem;
var revision = context.revision;
transaction {
var userWithLock = userModel.get(
id = user.id,
withLock = "readonly"
);
var poemWithLock = poemModel.get(
id = poem.id,
withLock = "readonly"
);
var revisionWithLock = revisionModel.get(
id = revision.id,
withLock = "exclusive"
);
revisionCascade.delete( userWithLock, poemWithLock, revisionWithLock );
}
}
}
As you can see, inside the transaction block, I'm re-reading the revision's parent poem row with a "readonly" lock. If the PoemService::update() method is executing concurrently, my attempt to gain a lock when calling RevisionService::delete() will block-and-wait for the update() transaction to be committed before it proceeds with the deletion workflow. This is because the PoemService::update() acquired an "exclusive" lock, which is incompatible with the "readonly" lock in the deletion workflow.
Again, locking doesn't work in isolation — it works if and only if other workflows agree to the same locking strategy.
Locking Simple Updates FOR UPDATE
If I need to update a single row, and the updating of that row doesn't involve any cross-row state logic, I wouldn't necessarily need to involve any locking. Technically, I wouldn't even need to include a transaction block since only one operation is being executed.
But — the implementation of my *Model::update() methods makes this a little complicated. My low-level update() methods try to make most of the arguments optional. And, for any argument that's omitted, I re-read the target row and fill-in the null arguments with the row's existing values.
To illustrate, here's a truncated view of the update() method in my CollectionModel.cfc ColdFusion component. Note that the only required argument is id — the rest of the arguments are optional; and, if omitted, they get pulled form the get(id) call that I make at the top of the method body:
component {
/**
* I update a model.
*
* Caution: this method should be called inside a transaction block in which the target
* row has obtained an exclusive lock. This ensures that the subsequent get() call used
* to fill-in null values will lead to a consistent read.
*/
public void function update(
required numeric id,
string name,
string descriptionMarkdown,
string descriptionHtml,
date updatedAt
) {
var existing = get( id );
name = isNull( name )
? existing.name
: validation.nameFrom( name )
;
descriptionMarkdown = isNull( descriptionMarkdown )
? existing.descriptionMarkdown
: validation.descriptionMarkdownFrom( descriptionMarkdown )
;
descriptionHtml = isNull( descriptionHtml )
? existing.descriptionHtml
: validation.descriptionHtmlFrom( descriptionHtml )
;
updatedAt = isNull( updatedAt )
? existing.updatedAt
: updatedAt
;
gateway.update(
id = existing.id,
name = name,
descriptionMarkdown = descriptionMarkdown,
descriptionHtml = descriptionHtml,
updatedAt = updatedAt
);
}
}
Since my model layer is calling get(id) in order to populate omitted arguments, it creates a race condition with the service layer, which also calls get(id) (the race condition is created between concurrent requests, not between the service/model layers). This is why my service layer ends up including a transaction block and an "exclusive" lock even for simple row updates:
component {
/**
* I update the given collection.
*/
public void function update(
required struct authContext,
required numeric id,
string name,
string descriptionMarkdown
) {
var context = collectionAccess.getContext( authContext, id, "canUpdate" );
var collection = context.collection;
if ( ! isNull( descriptionMarkdown ) ) {
var descriptionHtml = parseDescriptionMarkdown( descriptionMarkdown );
}
transaction {
var collectionWithLock = collectionModel.get(
id = collection.id,
withLock = "exclusive"
);
collectionModel.update(
id = collectionWithLock.id,
name = arguments?.name,
descriptionMarkdown = arguments?.descriptionMarkdown,
descriptionHtml = local?.descriptionHtml,
updatedAt = utcNow()
);
}
}
}
If I were relying on partial UPDATE SQL statements (ie, only updating some row columns), this locking wouldn't be necessary. However, since I'm sending all updatable columns to each UPDATE statement, locking becomes important due to the read-modify-update race condition across concurrent requests.
A Little Magic And a Lot Of Mutual Agreement
Row locking is powered by a little bit of database magic and lot of mutual agreement on how locking will be implemented throughout the application. The hard part of locking — in any context — is that if you do it wrong, nothing "blows up" — things just don't work the way you expected them to. With database locking, you might run into a deadlock; but, it's also possible that you'll just end up with lost updates and data inconsistencies that are hard to explain.
This is why I'm trying to err on the side of "rules" for how and when to do database row locking in my ColdFusion applications, even in places where it seems unnecessary. For me, rules are easy to stick to. I'm a Virgo and I crave consistency and structure. I'd rather have a little unnecessary locking than miss some locking that turns out to be critical.
If you're curious, here's the git diff for all the changes that I made in Big Sexy Poems in order to implement this locking:
It took me about a week of mornings to get this all in place.
Want to use code from this post? Check out the license.
Reader Comments
Post A Comment — ❤️ I'd Love To Hear From You! ❤️
Post a Comment →