Using SLEEP() And innodb_lock_wait_timeout To Force Transaction Lock Timeouts In ColdFusion And MySQL

lock.cfm

<!---
	In order to force a transaction lock timeout, we need to have two parallel
	queries that are trying to update the same row at the same time. As such, let's
	spawn a CFThread that initiates a LONG-RUNNING update using Sleep().
--->
<cfthread name="create-race-condition">

	<cfquery name="initialUpdate">
		UPDATE
			friend
		SET
			isBFF = SLEEP( 10 ) -- Will hang for 10-seconds.
		WHERE
			id = 5
	</cfquery>

</cfthread>

<!--- Sleep for a moment in order to ensure the above CFThread is spawned. --->
<cfset sleep( 100 ) />

<cftry>

	<!---
		Now that the above UPDATE is running (long), let's try to update the same row.
		By default MySQL will wait 50-seconds for a row-lock to timeout. Since I don't
		want to wait for that, I am using the "innodb_lock_wait_timeout" MySQL session
		variable in order to force the local lock timeout to be 2-seconds.
	--->
	<cfquery name="conflictingUpdate">
		SET SESSION innodb_lock_wait_timeout = 2; -- Is 50 by default.

		UPDATE
			friend
		SET
			isBFF = 1
		WHERE
			id = 5
		;
	</cfquery>

	<!---
		CAUTION: This does not work! The above query will throw a "Database" type
		error; I just wanted to make sure I couldn't catch it with a root-cause error
		type thrown by the database driver.
	--->
	<cfcatch type="com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException">

		<cfdump var="#cfcatch#" label="Java Type" />

	</cfcatch>

	<!---
		All database errors are wrapped in a "Database" error type, which we can catch.
		We can then inspect the Database error details to see what happened.
	--->
	<cfcatch type="Database">

		<!---
			The database can throw all kinds of errors. In this case, we only want to
			look at the ones that relate to "restarting" transactions. For this, we can
			look at error code "40001", which is the "ER_LOCK_DEADLOCK" error, and
			rethrow any errors that do not match.

			Read More: https://dev.mysql.com/doc/refman/5.7/en/error-messages-server.html#error_er_lock_deadlock
		--->
		<cfif ( cfcatch.errorCode neq "40001" )>

			<cfthrow />

		</cfif>

		<cfdump
			var="#cfcatch#"
			label="Database"
			show="detail,errorcode,exceptions,message,nativeerrorcode,sqlstate,type,queryerror"
		/>

	</cfcatch>

</cftry>

set-lock-timeout.sql

SET SESSION innodb_lock_wait_timeout = 2; -- Is 50 by default.