-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: Deadlock in MSSQL deleting objects from same table
PostPosted: Wed Mar 02, 2011 10:06 am 
Newbie

Joined: Mon Sep 24, 2007 8:11 am
Posts: 16
We have been using Hibernate (currently version 3.3.2) in our server for almost 3 years. We are recently seeing a deadlock problem when using MSSQL which I am able to reproduce with a simple program. I have not been able to reproduce this using Postgres as our DB.

The problem is as follows:
- I create 2 threads that each create a fairly simple object (User) that has a ManyToOne reference to another simple object with CascadeType.ALL as well as Hibernate's CascadeType.DELETE_ORPHAN
- The threads commit the transaction to persist the 2 Users into the DB
- The threads create a new transaction and delete the User object (the child object is deleted by Hibernate thru cascade)
- The threads then synchronize in order to commit the new transaction at the same time
- About 90% of the time I see the following error (PlatformPolicies is the child object):
- I did not see the problem if I did not add the child object to the User object

Quote:
2011-03-01 20:56:59,449 ERROR: Transaction (Process ID 81) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [JDBCExceptionReporter[Thread-1]]
2011-03-01 20:56:59,450 ERROR: Could not synchronize database state with session [AbstractFlushingEventListener[Thread-1]]
org.hibernate.exception.LockAcquisitionException: could not delete: [com.xxx.mgmt.api.policy.PlatformPolicies#34]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:82)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2546)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2702)
at org.hibernate.action.EntityDeleteAction.execute(EntityDeleteAction.java:77)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:172)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at com.xxx.mgmt.api.TxFactory.commitTransaction(TxFactory.java:317)
at com.xxx.mgmt.api.testing.testUpdate$TestRunner.run(testUpdate.java:100)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 81) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(Unknown Source)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2528)
... 10 more


My 2 classes are annotated are as follows:
Code:
@Entity
@Table(name = "User_Table")
public class User extends NamedObject implements java.io.Serializable
{
    .
    .
    .
    @ManyToOne(optional = true, cascade = {CascadeType.ALL})
    @org.hibernate.annotations.Cascade({
        org.hibernate.annotations.CascadeType.SAVE_UPDATE,
        org.hibernate.annotations.CascadeType.DELETE_ORPHAN
        })
    private PlatformPolicies platformPolicies = null;

}



My simple program to reproduce this is:
Code:

public class testUpdate
{
    private static final Log LOG = Factory.getLog(TestCreate.class, LoggableComponent.API);

    public static void main(String[] args)
    {
        TestRunner t1 = new TestRunner("aaaaaaaaa");
        TestRunner t2 = new TestRunner("bbbbbbbbb");
        t1.start();
        t2.start();
    }

    public static class TestRunner extends Thread
    {
       
        String name = null;
       
        public TestRunner(String name)
        {
            this.name = name;
        }
        private int NUM_USERS = 1;
        @Override
        public void run()
        {
            long[] ids = new long[NUM_USERS];
            TxFactory txFactory = TxFactory.getInstance();
            String tx = txFactory.createTransaction();
            try
            {
                Session session = txFactory.getSession(tx);
                // Create a user - use a loop so we can test creating multiple users
                for (int ii=0; ii < NUM_USERS; ii++)
                {
                    User user = new User(name + ii, "", "", "");
                    Map<PolicyType, Policy> policies = new HashMap<PolicyType, Policy>();
                    user.setPlatformPolicies(policies);
                    LOG.info("Creating user " + user.getName());
                    ids[ii] = (Long)session.save(user);
                    LOG.info("Created user " + user.getName());
                }
                LOG.info("Committing xaction for create " + name);
                txFactory.commitTransaction(tx);
                LOG.info("Committed xaction for create " + name);
               
                tx = txFactory.createTransaction();
                session = txFactory.getSession(tx);
                // Delete user we just created
                for (int ii=0; ii < NUM_USERS; ii++)
                {
                    User user = (User)session.load(User.class, ids[ii]);
                    LOG.info("Deleting user " + user.getName());
                    session.delete(user);
                    LOG.info("Deleted user " + user.getName());
                }
               
                // Synch with other threads
                synchWait();

                // Commit the xaction to perform the delete
                LOG.info("Committing delete xaction");
                txFactory.commitTransaction(tx);
                LOG.info("Committed delete xaction");
                tx = null;
            }
            catch (Throwable t)
            {
                if (tx != null)
                {
                    txFactory.rollbackTransaction(tx);
                }
                LOG.error("Error in TestRunner", t);
            }
        }
    }

    private final static Object updateSynch = new Object();
    private static int synchCounter = 0;
    // Synchronize 2 threads - 2nd one in wakes the 1st
    private static void synchWait()
    {
        synchCounter++;
        if (synchCounter == 2)
        {
            synchronized(updateSynch)
            {
                synchCounter = 0;
                LOG.info("Notifying updateSynch");
                updateSynch.notify();
            }
        }
        else
        {
            synchronized(updateSynch)
            {
                try
                {
                    LOG.info("Waiting on updateSynch");
                    updateSynch.wait();
                }
                catch(Throwable t) {LOG.error("Error in updateSynch: " + t.getClass() + " - " + t.getMessage(), t);}
            }
        }
    }
}


A few things to note:
- The code is written to create/delete multiple users but I can reproduce the problem with the count set to 1
- The synchWait() method is to force the 2 threads to perform their commits simultaneously (this is running on a multi-processor)
- The PlatformPolicies child object that is created is essentially an empty object - a row with a generated ID and nulls in all other columns is created in the table and the User object references it
- We first noticed this when we ran our product on different servers accessing the same database. So it happens in a multi-threaded environment as well as with distributed processes.
- I logged the SQL code thru Hibernate and saw the following prior to the exception:
Quote:
Hibernate:
/* delete com.xxx.mgmt.api.User */ delete
from
User_Table
where
id=?
and revision=?
Hibernate:
/* delete com.xxx.mgmt.api.policy.PlatformPolicies */ delete
from
PlatformPolicies
where
id=?
and revision=?
Hibernate:
/* delete com.xxx.mgmt.api.policy.PlatformPolicies */ delete
from
PlatformPolicies
where
id=?
and revision=?



Any help is greatly appreciated.

-Peter


Top
 Profile  
 
 Post subject: Re: Deadlock in MSSQL deleting objects from same table
PostPosted: Wed Mar 02, 2011 9:45 pm 
Newbie

Joined: Mon Sep 24, 2007 8:11 am
Posts: 16
To better understand what's going on here, I modified the delete part of my program to use JDBC as follows:
Code:
                tx = txFactory.createTransaction();
                session = txFactory.getSession(tx);
                // Delete user we just created
                User user =  (User)session.load(User.class, ids[0]);
                PlatformPolicies pp = user.getPlatformPolicies();
                txFactory.commitTransaction(tx);
               
                List<String> queries = new ArrayList<String>();
                queries.add("Delete from user_table where id = " + user.getId() + " and revision = "
                        + user.getRevision());
                queries.add("Delete from platformPolicies where id = " + pp.getId() + " and revision = "
                        + pp.getRevision());
                synchWait();
                executeBatch(queries);


executBatch() creates a java.sql.Connection and uses java.sql.Statement.add/executeBatch() to execute the SQL statements.
My logging indicates that the statements were sent to the DB simultaneously.
This code was successful every time I ran it.
Unfortunately, the code paths where we are seeing the deadlock in our product are adding and deleting a fairly complex tree of objects which would be too difficult to convert to using JDBC.

I also ran SQL Server Profiler and it logged the deadlock describing it as a KeyLock on the User_table using the PK which is a clustered index.
Not sure exactly what that tells me, tho I think I need to find a MSSQL forum for help on that.


Top
 Profile  
 
 Post subject: Re: Deadlock in MSSQL deleting objects from same table
PostPosted: Thu Mar 03, 2011 7:34 pm 
Newbie

Joined: Mon Sep 24, 2007 8:11 am
Posts: 16
Just tried my sample program using Hibernate 3.6.1 Final - still deadlocks.


Top
 Profile  
 
 Post subject: Re: Deadlock in MSSQL deleting objects from same table
PostPosted: Thu Mar 10, 2011 12:17 pm 
Newbie

Joined: Thu Mar 10, 2011 11:18 am
Posts: 1
The issue is resolved by generating an index on the "User" class's "platformPolicies" field.

The following example explains why, to the best of my ability ;-)

Code:
public class A
{
    @OneToOne(optional = true, cascade = CascadeType.ALL)
    @Cascade( { org.hibernate.annotations.CascadeType.DELETE_ORPHAN })
    // @Index(name="IDX_A_B")
    private B b;
}

If an instance of class A "a" is deleted, its instance of class B "b" is also deleted because of the DELETE_ORPHAN cascade.

SQL Server Profiler showed that two threads had an exclusive (X) lock on the primary key of table A -- this was because of the deletion of instance "a".

SQL Server Profiler also showed that the two threads were requesting a shared (S) lock on the same primary key of table A -- this was the cause of the deadlock.

When instance "b" was deleted by cascade, because of the foreign key relationship / constraint between tables A and B, SQL Server must validate that no instances of A refer to the instance "b" being deleted. Because of the lack on an index, it does this by querying table A for references to the instance "b", thereby requesting a shared lock on the primary key of table A.

Adding index IDX_A_B enables SQL Server to determine if the instance "b" is referenced by any instances of A without a general query on table A, thereby avoiding the need to acquire the shared lock, and avoiding the deadlock.

As many have suggested elsewhere, an index is often a way to avoid deadlock.

Once the SQL Server Profiler clearly showed the deadlock, Googling on "sql server primary key exclusive shared deadlock" generated sufficient hits to solve the problem. The following blog was the critical read -- see the section "Deadlock II":

http://manycupsofcoffee.blogspot.com/20 ... erver.html


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.