Resolving ORA-000060 Deadlock in Oracle Batch Updates with MyBatis

During production a deadlock error ORA-000060 was raised by Oracle when a program read a large file (tens of thousands of lines) and, for every 500 rows, dispatched a thread to batch‑update the database using the primary key.

The target table has the following structure:

user_id (PK)

user_name

age

sex

00001

tom

6

man

00002

jimi

11

woman

The batch‑update is performed with the following MyBatis XML snippet:

<update id='updateUser' parameterType='java.util.List'>
  <foreach collection='list' item='item' index='index' open='' close='' separator=';'>
    update tb_user set user_name=#{item.userName} age = #{item.age} where user_id= #{item.userId}
  </foreach>
</update>

After consulting DeepSeek, the likely cause of the deadlock was identified: the input file contains duplicate user_id values and is not sorted by user_id . Different threads therefore try to lock the same rows in opposite order, leading to a lock wait.

Two example thread executions illustrate the problem:

Thread 1:

update tb_user set user_name=#{item.userName} age = #{item.age} where user_id = '00001';
update tb_user set user_name=#{item.userName} age = #{item.age} where user_id = '00002';

Thread 2:

update tb_user set user_name=#{item.userName} age = #{item.age} where user_id = '00002';
update tb_user set user_name=#{item.userName} age = #{item.age} where user_id = '00001';

Verification of the file confirmed the presence of such duplicate and unsorted records.

Based on the analysis, the following solutions were proposed:

Ask the upstream system to sort the file data by user_id before delivery.

Later, deduplicate records with the same user_id , keeping only the most recent entry.

DeepSeek also provided additional visual guidance (images omitted here) and a detailed deadlock‑prevention checklist.

Finally, the official Oracle description of the ORA-000060 exception is referenced.