‘SQL error 60 when accessing table’ run time error

‘SQL error 60 when accessing table’ run time error
3 (60%) 2 votes

You can see this type of run time errors under DBIF_RSQL_SQL_ERROR. The main cause for this run time error is database system goes into deadlock situation while waiting resource. A deadlock situation occurs if a database transaction is blocked when it requests a database lock but it already has another lock. I know this explanation is very complicated. Now I will try to explain this with a simple example i have experienced lately.

Suppose that you have a delete SQL statement which has a where condition a_field = ‘X’ or b_field = ‘X’. Here some rows may fulfill both a_field = ‘X’ and b_field = ‘X’ conditions and that records will cause a deadlock as i will explain soon.

story of a deadlock

  • Executing ‘delete from’ statement is in parallel and page by page applied by database engine to delete all records fulfilling the conditions in z_table when there are extensive records fitting the required conditions. Executing in packages in parallel is not a special case for this SQL , but as there is OR operator things become strange.
  • Lets say first cursor tries to delete records where a_field = ‘X’ so it puts locks on this records.
  • Meanwhile deletion from z_table continues parallelly so a second parallel ‘delete from’ command tries to delete remaining records from z_table.
  • But as the first command has locked some of a_field = ‘X’ records , second ‘delete from’ statement tries to put locks on remaining records.
  • Think that second cursor has put locks on b_field = ‘X’ fields. Because there are records fitting both conditions (a_field = ‘X’ and b_field = ‘X’) second and first cursor tries to put locks on the same records.
  • This situation cause two different SQL commands to lock and wait for each other which is named as deadlock in computer terminology.

Solution for this error is to separate this SQL statements like the following.

Apart from this meaningful cases, there can be weird ones and SAP has published a note about these cases.

SAP Note 84348

If a data record is locked in the database, this lock information is written to the block. The space provided in the block header is used for this purpose. The space is defined when the table is created with the parameters INITRANS and MAXTRANS. If a block becomes too full, the database cannot allocate any more space to store this block and must wait for memory space in the corresponding block. As a result, row level locking can become block level locking.
If some parallel scripts now lock a large number of data records that are in the same block, two or more of the scripts may sometimes cause a deadlock, not because they lock the same data record, but because no additional memory space can be allocated for the lock.

To summarize deadlock errors mostly caused by update, delete, modify of extensive records. In every case you should avoid mass SQL operations on database and separate SQL statements by ‘commit work’ statements where you operate on same data set.

You may also like