使用JPA Query注解操作Oracle 11g的更新语句执行停滞

Hey there, let's break down why your JPA update is hanging even though the same SQL runs fine in Oracle SQL Developer. This is almost always related to transaction locks or how Spring Boot manages transactions vs. your standalone SQL Dev session. Here's what to check and fix:

1. First, Verify if It's a Table/Row Lock Issue #

Since the SQL works in SQL Dev but hangs in Spring Boot, the most likely culprit is a row lock held by another session (probably your Spring Boot app itself). To confirm this, run this query in SQL Dev to check locks on your IRREGULARITIES table:

SELECT 
    l.session_id AS sid,
    s.serial#,
    l.locked_mode,
    l.oracle_username,
    l.os_user_name,
    s.machine,
    s.program,
    o.object_name,
    s.status
FROM 
    v$lock l
JOIN 
    v$session s ON l.session_id = s.sid
JOIN 
    dba_objects o ON l.id1 = o.object_id
WHERE 
    o.object_name = 'IRREGULARITIES';

Look for sessions where program matches your Spring Boot app. If you see a session with locked_mode = 6 (exclusive lock) on the row with IRREGS_ID = 1006, that's the lock causing your update to hang.

What to do next: #

• If the locked session is from your app, check if there's a long-running transaction that never committed (maybe an unhandled exception that left locks held, though this is rare).
• You can kill the locked session temporarily to test (replace [SID] and [SERIAL#] with values from the query above):

ALTER SYSTEM KILL SESSION '[SID],[SERIAL#]';

Note: Only do this in non-production or with approval—killing sessions can disrupt ongoing work.

2. Check Transaction Scope & Propagation #

Your updateState() method uses @Transactional, but if this method is called from another method with @Transactional, it will inherit that parent transaction. If the parent transaction has other operations (like a SELECT ... FOR UPDATE on the same row) and takes a long time to commit, the row lock will be held, blocking your update.

Fix: Force a New Transaction #

Change the transaction propagation to REQUIRES_NEW to ensure this update runs in its own independent transaction that commits immediately after execution:

@Transactional(propagation = Propagation.REQUIRES_NEW)
@Modifying(clearAutomatically = true)
@Query(value="UPDATE Irregularities SET IRREST_ID = 0 WHERE IRREGS_ID = 1006", nativeQuery = true)
int updateState();

The clearAutomatically = true also helps clear JPA's persistence context, preventing stale entity caches from interfering with the native update.

3. Check JPA Persistence Context Caching #

If your app previously loaded the IRREGULARITIES entity with IRREGS_ID = 1006 into the JPA first-level cache, it might be holding a lock or causing consistency checks that block the native update. Adding clearAutomatically = true to @Modifying (as above) clears this cache, ensuring the native query runs without interference.

4. Investigate Database Connection Pool Issues #

Sometimes, connection pool exhaustion or stuck connections can cause queries to hang. Check your app's connection pool settings (e.g., HikariCP if you're using the default Spring Boot pool):

• Ensure there are enough idle connections available
• Verify that connections are being properly released after transactions complete
• Check if any connections are stuck in a "WAITING" state (use Oracle's v$session to see session status)

5. Check Oracle Session Wait Events #

To get more details on why your app's session is hanging, run this query to see what it's waiting for:

SELECT sid, event, wait_time, seconds_in_wait 
FROM v$session_wait 
WHERE sid = [your app's session ID from the first lock query];

If you see enq: TX - row lock contention, that confirms it's a row lock issue. If the wait event is something else (like db file sequential read), it might be a disk I/O issue, but that's less likely since SQL Dev works fine.

内容的提问来源于stack exchange，提问作者Sebastian Moreno E