Folks,
I am using cftransaction on CF 5.0 paired with SQL Server 2000 to pick up a particular customer call record from within a table, then updating that record to a 'locked' status so that it cannot be picked up by anyone else. I keep getting deadlocks when two users hit the .cfm template at the same millisecond. Here is my code:
<cfset CommitThis = True>
<cftransaction action="begin" isolation="serializable">
<cftry>
<cfquery datasource="#DSN#" name="GetFirst">
SELECT Min(ID) ThisID FROM RepairCalls
WHERE CampaignID = #CampaignID#
AND
(
Status = 14 OR (Status IN (8,16) AND DateUpdated < DateAdd(hh,-1,GetDate()) AND Attempts < 3)
)
</cfquery>
<!--- If last record already picked up, redirect to campaign list --->
<cfif NOT IsNumeric(GetFirst.ThisID)>
<cftransaction action="rollback"></cftransaction>
<cflocation url="RepairStatus.cfm?CampaignID=#CampaignID#" addtoken="no">
<cfabort>
</cfif>
<cfquery datasource="#DSN#" name="SetInProcess">
UPDATE RepairCalls SET Status = 5,
UpdatedBy = '#Request.UID#',
DateUpdated = GetDate()
WHERE ID = #GetFirst.ThisID#
</cfquery>
<cfcatch type="database">
<cfset CommitThis = False>
</cfcatch>
</cftry>
<cfif CommitThis EQ True>
<cftransaction action="commit"></cftransaction>
<cfelse>
<cftransaction action="rollback"></cftransaction>
<cflocation url="RepairStatus#Suffix#.cfm?CampaignID=#CampaignID#" addtoken="no">
<cfabort>
</cfif>
</cftransaction>
I've attempted to catch database errors such as the deadlock, but errors still appear in the application log. Is this the case even when using cfcatch?
I wonder if I should use try to make everything one query and use the select query as a subquery of the update. Does anyone have experience with this issue?
I'd read to use isolation of serializable in this scenario. I've tried different isolation levels but that doesn't seem to have an effect. Additionally, I understand that a transaction handling hint handed to SQL Server may be overridden if the server determines another isolation level is of lower cost. Anyone have thoughts / experiences on this piece?
I am using cftransaction on CF 5.0 paired with SQL Server 2000 to pick up a particular customer call record from within a table, then updating that record to a 'locked' status so that it cannot be picked up by anyone else. I keep getting deadlocks when two users hit the .cfm template at the same millisecond. Here is my code:
<cfset CommitThis = True>
<cftransaction action="begin" isolation="serializable">
<cftry>
<cfquery datasource="#DSN#" name="GetFirst">
SELECT Min(ID) ThisID FROM RepairCalls
WHERE CampaignID = #CampaignID#
AND
(
Status = 14 OR (Status IN (8,16) AND DateUpdated < DateAdd(hh,-1,GetDate()) AND Attempts < 3)
)
</cfquery>
<!--- If last record already picked up, redirect to campaign list --->
<cfif NOT IsNumeric(GetFirst.ThisID)>
<cftransaction action="rollback"></cftransaction>
<cflocation url="RepairStatus.cfm?CampaignID=#CampaignID#" addtoken="no">
<cfabort>
</cfif>
<cfquery datasource="#DSN#" name="SetInProcess">
UPDATE RepairCalls SET Status = 5,
UpdatedBy = '#Request.UID#',
DateUpdated = GetDate()
WHERE ID = #GetFirst.ThisID#
</cfquery>
<cfcatch type="database">
<cfset CommitThis = False>
</cfcatch>
</cftry>
<cfif CommitThis EQ True>
<cftransaction action="commit"></cftransaction>
<cfelse>
<cftransaction action="rollback"></cftransaction>
<cflocation url="RepairStatus#Suffix#.cfm?CampaignID=#CampaignID#" addtoken="no">
<cfabort>
</cfif>
</cftransaction>
I've attempted to catch database errors such as the deadlock, but errors still appear in the application log. Is this the case even when using cfcatch?
I wonder if I should use try to make everything one query and use the select query as a subquery of the update. Does anyone have experience with this issue?
I'd read to use isolation of serializable in this scenario. I've tried different isolation levels but that doesn't seem to have an effect. Additionally, I understand that a transaction handling hint handed to SQL Server may be overridden if the server determines another isolation level is of lower cost. Anyone have thoughts / experiences on this piece?
