Feedback
Did this article resolve your question/issue?

   

Article

Avoiding error "Failure to get record lock on a record from table <tablename>" when running SELECT queries

« Go Back

Information

 
TitleAvoiding error "Failure to get record lock on a record from table <tablename>" when running SELECT queries
URL NameP12158
Article Number000144788
EnvironmentProduct: Progress
Version: 9.1x
Product: OpenEdge
Version: 10.x, 11.x
OS: All Supported Operating Systems
Other: SQL-92
Question/Problem Description
Error occurs when running a SELECT query via JDBC or ODBC.

Error indicates a failure to get a record lock.

Error occurs when isolation level is set to one of the following:
  • Read Committed
  • Repeatable Read
  • Serializable
Steps to Reproduce
Clarifying Information
Error MessageODBC Error Code = S1000 (General error)
[DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]Failure getting record lock on a record from table <schema>.<tableName>
Defect Number
Enhancement Number
Cause
SELECT statements are being run while connected with Read Committed, Repeatable Read, or Serializable set as the Isolation Level. These isolation levels will place a share lock on the record while it is being read.
Resolution
Option #1 - applies from OpenEdge 10.1A02 and later.
Use the "WITH (NOLOCK)" hint in the SELECT query. This ensures that no record locks are acquired. For example,

SELECT * FROM pub.customer WITH (NOLOCK);

The "WITH (NOLOCK)" hint is similar to using the Read Uncommitted isolation level in that it will result in a dirty read.


Option #2 - applies to all OpenEdge (10.x/11.x) versions using the Read Committed isolation level.
Use the "WITH (READPAST)" hint in the SELECT query. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. For example,

SELECT * FROM pub.customer WITH (READPAST NOWAIT);
SELECT * FROM pub.customer WITH (READPAST WAIT 5);

Please be aware that this can lead to fewer records being returned than expected since locked records are skipped/omitted from the result set.


Option #3 - applies to all Progress/OpenEdge versions.
Change the Isolation Level to Read Uncommitted to ensure that, when a record is read, no record locks are acquired. Using the Read Uncommitted isolation level will result in a dirty read.
This can be done at ODBC DSN level or via the SET TRANSACTION ISOLATION LEVEL <isolation_level_name> statement. For example,

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 
Workaround
Notes
References to other documentation:

OpenEdge® Data Management: SQL Development, Chapter 8: Data Control Language and Transaction Behavior > Enhancing performance with locking hints

For additional information about Locking, see the Locking Whitepaper available at: https://community.progress.com/technicalusers/w/openedgegeneral/1915.locking.aspx

Progress article(s):
"Transaction Isolation Level: Proper Syntax and Definitions"

Third-party article(s):
J2EE Isolation Levels - http://docs.oracle.com/javaee/1.4/tutorial/doc/Transaction7.html
Last Modified Date11/20/2020 7:17 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.