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.