Record locking problems when using cftransaction

dev support
1

I’m troubleshooting some unexpected record locking issues in our application. Basically, it seems like using cftransaction tags is resulting in record locks being applied and/or held in ways I wouldn’t expect.

I’m not sure whether this is an issue with Lucee, our JDBC driver, or our back-end database. The database is Progress OpenEdge, and the JDBC driver is Progress’s JDBC driver.

I’ve tried to simplify things as much as possible in this post, and have the following test code that duplicates the issue. In it, I have two cfm files.

locktest1.cfm has some code within a cftransaction block. I’ve removed the record update/create logic from the production code, leaving only a query. The datasource uses a read-uncommitted isolation level. However, this seems to be overridden by the isolation level specified in the cftransaction tag.

<cftransaction isolation = "read_committed">
<cfquery name="qrytest" datasource="mo_opm_uncomm">
    SELECT uw_site from uw_mstr where uw_site = 22291 with (nolock)
</cfquery>

<cfdump var="#qrytest#" />
</cftransaction>

locktest2.cfm does only a query of the same record, with no cftransaction block.

<cfquery name="qrytest" datasource="mo_opm_uncomm">
    SELECT uw_mstr.rowid, uw_site from uw_mstr where uw_site = 22291 with (nolock)
</cfquery>

<cfdump var="#qrytest#" />

In our problem scenario, a user has an exclusive lock on the record in question (site 22291) in our main business application, not the Lucee web site.

If I then browse to locktest1.cfm, the site # field from record 22291 is displayed.

If I then browse to locktest2.cfm, I get the following Lucee error about a failure to get a record lock.

    Lucee 6.1.1.118 Error (database)
    [DataDirect][OpenEdge JDBC Driver][OpenEdge] Failure getting record lock on a record from table PUB.uw_mstr.

    SQL SELECT uw_mstr.rowid, uw_site from uw_mstr where uw_site = 22291 with (nolock)

I wouldn’t expect this kind of record lock failure on a select using a read-uncommitted isolation level. I would expect the select to return the fields, regardless of whether there is a lock elsewhere on the record.

This problem only happens if there is a cftransaction tag in locktest1.cfm. If I remove the cftransaction tag from locktest1.cfm and re-run the test, browsing to both .cfm files displays the expected select results, with no Lucee errors. So, the problem is somehow related to the cftransaction tags.

On our production site, this is resulting in users getting these Lucee record lock errors when running code that should just be returning select query values.

Don’t forget to tell us about your stack!

OS: AlmaLinux 9.5
Java Version: 11.0.25
Tomcat Version: 9.0.97
Lucee Version: 6.1.1.118

This was previously occurring on an older Lucee 5.3 version of our site, so it’s not specific to 6.x.

I’m looking for any guidance as to whether this is expected results, and, if not, where I should be looking for the root cause of the problems.

2

I’m probably not fully comprehending the issue but just in case … maybe you need to commit the transaction?

transaction {
	try {
		// queries here
		transaction action="commit";
	}
	catch(any error) {
		transaction action="rollback";
		throw error;
	}
}
3

Thanks for the suggestion. I tried adding a commit during my initial troubleshooting, and it made no difference. The locking issue still occurred.

4

Oh sorry, I was referring to the update/create logic you mentioned happening prior to the select, as I have only ever used cftransactions for grouping series of inserts for atomicity. Why do you need a cftransaction for the select?

5

That was just a very stripped down example to keep my posting short. The actual production code has a cftransaction around a loop. Within that loop there are a number of update, insert, and select statements. We need to have the entirety of all iterations of the loop either done or undone completely, which is why we have the cftransaction around it.

Putting a commit at the end of the cftransaction block, after the last iteration of the loop, didn’t fix the issue.

What we’ll probably end up needing to do is refactor the code in some way to move the select(s) that are causing the later record locking problem outside of the cftransaction block.

1 Like