How to Fix Oracle ORA-000060 Deadlock in Bulk User Updates

Hello, I am Su San. Recently I ran into a deadlock in production and Oracle threw the ORA-000060 exception.

Business scenario: The program reads a file delivered by an upstream system containing tens of thousands of rows. After reading, every 500 rows are assigned to a thread to batch‑update the database using the primary key. The table has columns user_id (PK), user_name , age , and sex .

<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 the issue appeared, we consulted DeepSeek for help. Its deep reasoning suggested that once the deadlock cause is identified, the fix may involve adjusting transaction order so that different sessions access tables in the same sequence, reducing cross‑lock possibilities.

Root cause speculation: The file likely contains duplicate user_id values and is not sorted by user_id . When multiple threads update rows with the same IDs in different orders, lock waiting occurs. For example, two threads might execute:

-- 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';

Indeed, the file data shows this situation.

After locating the problem, the solution becomes straightforward:

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

Later, optimize by keeping only the most recent record for each duplicate user_id .

DeepSeek also provided a detailed diagram of the deadlock resolution method (see image below).

Finally, the Oracle official description of the ORA-000060 exception is shown in the following image.