CODEWITHSUNDEEP
javamultithreadingoracle-databasetransactions
Pokuri
Pokuri

Reputation: 3082

validate my understanding about DB Locks and threads

I have observed following error logs in one of log files

2012-01-31 10:52:46,424 IST WARN           "SQL Error: 2049, SQLState: 42000"
2012-01-31 10:52:46,424 IST ERROR          "ORA-02049: timeout: distributed ransaction waiting for lock"
2012-01-31 10:52:46,440 IST ERROR          "Could not synchronize database state with session"
org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)

I have class starting a Transaction in which it gets a table LOCK, in the same block of code, as per requirement, we need to start a new transaction which is about to acquire the LOCK on same table it seems. In that case is there a possibility of deadlock and which will be break/resolved by this Transaction Timeout exception.

Inner transaction code here:

private void createXYZ(final Object tabPortletObject){

        try {
            //get the template
            TransactionTemplate transactionTemplate = getTransactionTemplate();
            //create a new transaction template with propagation behavior as requires new
            TransactionTemplate newTransactionTemplate = new TransactionTemplate();
            newTransactionTemplate.setTransactionManager(transactionTemplate.getTransactionManager());
            newTransactionTemplate.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
            //start a new transaction and set the status in that new transaction
            newTransactionTemplate.execute(new TransactionCallbackWithoutResult() {
                public void doInTransactionWithoutResult(TransactionStatus transactionStatus) {
                    getHibernateTemplate().save(tabPortletObject);
                }
            });

        } catch (Exception e) {
            logger.info("Exception occured while creating [Tab/Portlet] :" + tabPortletObject);
        }
    }

And I have outer transaction which will be started in servlet filter.

My assumption is as inner transaction is waiting for the LOCK acquired by Outer transaction and that outer transaction is waiting to complete executing that block of code to commit the transaction, in which case that LOCK won't be released, leading to a deadlock. Ultimately that inner transaction getting timed out exception to break that deadlock.

Is my statement valid?

This can be resolved if I start a new/inner Transaction in different thread?

Hope you understand it as I am not good at explaining it!

Upvotes: 0

Views: 3630

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300489

"This can be resolved if I start a new/inner Transaction in different thread?"

  • No.

If this is true:

I have class starting a Transaction in which it gets a table LOCK, in the same block of code, as per requirement, we need to start a new transaction which is about to acquire the LOCK on same table it seems

then you appear to have a fundemental design flaw.

You either need to hold a less restrictive transaction isolation level, or redesign your logic (or poosibly both).

Upvotes: 2

Related Questions