Locked record errors -244 and -107 or -245 and -144 when updating a record

Problem(Abstract)

Locked record errors -244 and -107 or -245 and -144 when updating a record

Symptom

-244 Could not do a physical-order read to fetch next row.
-107 ISAM error: record is locked.

-245 Could not position within a file via an index.
-144 ISAM error: key value locked.

-271 Could not insert new row into the table.
-144 ISAM error: key value locked.

Cause

Row, page, or table is locked.

Diagnosing the problem

onstat -k will show you the locks

• The owner column of onstat -k has the same value as the onstat -u column address. The onstat -u output should help you identify the owner of the locks in onstat -k. The owner's username will be listed in the user column of onstat -u The onstat - u output also has a sessid column. You can use the sessid value to find out more about the session that holds the lock. Run onstat -g ses <sessid value>.
  
• The tblsnum column of onstat -k is the hexadecimal tablespace number ( part number, partnum ). You will use this value to identify the table or index that is locked. How to find a table name based on partnum

• Additional information regarding onstat -k found in the Information Center here, Monitoring Locks

Resolving the problem

• Set Lock Mode to wait;
  SET LOCK MODE statement syntax 
  
• Set Lock Mode to wait n;
  
  n = number of seconds to wait
  SET LOCK MODE statement syntax 
  
• Set row level locking and use BEGIN WORK statement.
  LOCK MODE Clause (How to set row level locking for a table) 
  
• Consider using 'Committed Read' isolation level and the use of USELASTCOMMITTED parameter in onCONFIG file. When using this isolation mode and USELASTCOMMITTED is set to 'committed read' in onCONFIG, the last committed version of the data will be returned avoiding lock contention. USELASTCOMMITTED only works with table that has row level locking. 
  USELASTCOMMITTED Configuration Parameter 
  
  Note: USELASTCOMMITTED is a new feature in IDS 11, and is not available in IDS 10 or lower. 
  
  

https://www-304.ibm.com/support/docview.wss?uid=swg21327818