使用jTDS+Hibernate时提交语句关闭后MSSQL2008事务未终止求助

Alright, let's tackle this sleeping/AWAITING COMMAND transaction issue you're facing with jTDS 1.3.1, Hibernate 3, and MSSQL Server 2008+. I've debugged similar connection leakage scenarios before, so here's a step-by-step breakdown of fixes you can implement:

1. Shrink Transaction Boundaries for Batch Processing #

The root cause here is holding a single connection/transaction open for the entire large batch. Instead, split your batch into smaller chunks and commit/close resources after each chunk to free up connections immediately.

Here's a revised code pattern to follow:

private static final int BATCH_SIZE = 500; // Adjust based on your data size

public void processLargeDataSet(List<YourEntity> largeDataSet) {
    Session session = null;
    Transaction tx = null;

    try {
        for (int i = 0; i < largeDataSet.size(); i++) {
            // Start new session/transaction every BATCH_SIZE records
            if (i % BATCH_SIZE == 0) {
                // Clean up previous resources
                if (session != null) {
                    tx.commit();
                    session.close();
                }
                // Initialize new session and transaction
                session = HibernateUtil.getSessionFactory().openSession();
                tx = session.beginTransaction();
            }

            // Process current entity
            session.saveOrUpdate(largeDataSet.get(i));

            // Flush and clear session to avoid memory bloat
            if (i % BATCH_SIZE == BATCH_SIZE - 1) {
                session.flush();
                session.clear();
            }
        }

        // Commit the final partial batch
        if (tx != null && !tx.wasCommitted()) {
            tx.commit();
        }
    } catch (Exception e) {
        // Always rollback on failure to avoid hanging transactions
        if (tx != null && !tx.wasRolledBack()) {
            tx.rollback();
        }
        throw new RuntimeException("Batch processing failed", e);
    } finally {
        // Ensure session is closed even if errors occur
        if (session != null && session.isOpen()) {
            session.close();
        }
    }
}

Key points here:

• Never hold a single session open for thousands of records
• Explicitly commit/rollback transactions in every code path
• Use flush() + clear() to prevent Hibernate's first-level cache from overflowing

2. Tune jTDS Connection Properties #

jTDS has specific flags that can prevent hanging connections. Add these to your Hibernate JDBC URL:

jdbc:jtds:sqlserver://your-server:1433/your-db;useCursors=false;socketTimeout=300000;xactAbort=true;loginTimeout=60

• useCursors=false: Disables server-side cursors, which can leave connections in a sleeping state if not properly closed
• socketTimeout=300000: Forces idle connections to close after 5 minutes (adjust based on your batch runtime)
• xactAbort=true: Tells MSSQL to automatically rollback transactions and close connections if an error occurs (prevents orphaned transactions)

3. Configure Connection Pooling Properly #

If you're using Hibernate's built-in C3P0 pool (common in Hibernate 3), add these properties to your hibernate.cfg.xml to enforce connection cleanup:

hibernate.connection.provider_class=org.hibernate.connection.C3P0ConnectionProvider
hibernate.c3p0.maxIdleTime=1800  // Recycle connections after 30 minutes of idle
hibernate.c3p0.timeout=300       // Destroy connections that are idle for 5 minutes
hibernate.c3p0.validate=true      // Validate connections before borrowing from the pool
hibernate.c3p0.testOnBorrow=true  // Test connections to ensure they're not holding open transactions

If you're using another pool (like DBCP), set equivalent parameters (maxIdle, minEvictableIdleTimeMillis, testOnBorrow) to keep the pool clean.

4. Fix Transaction Isolation and Database Settings #

• Ensure your Hibernate transaction isolation level matches MSSQL's default. Add this to your config:

  hibernate.connection.isolation=2  // Corresponds to READ COMMITTED (MSSQL's default)
  

• Enable MSSQL's SET XACT_ABORT ON globally for your connection. You can add this to Hibernate's init SQL:

  hibernate.connection.init_sql=SET XACT_ABORT ON;
  

This ensures any failed transactions are immediately rolled back instead of lingering in a sleeping state.

5. Hunt for Hidden Transaction Leaks #

• Check all exception handling blocks: If you catch an error but don't explicitly call tx.rollback(), the transaction will stay open indefinitely.
• Avoid using session.beginTransaction() without pairing it with a commit/rollback. Hibernate 3 does not automatically rollback uncommitted transactions when closing a session.
• Audit any utility methods that handle sessions/transactions—make sure they don't leave resources hanging.

By implementing these changes, you'll eliminate the sleeping/AWAITING COMMAND transactions by ensuring every connection is properly released and every transaction is either committed or rolled back promptly.

内容的提问来源于stack exchange，提问作者Joseph Peruzzi