CODEWITHSUNDEEP
c#sql-server-2008nhibernate
embarus
embarus

Reputation: 815

Nhibernate two transaction read the same table and insert the same table make one transaction fail

User Table structure Id Username (unique constrain)

I have the problem with Nhibernate and SqlServer like this. There are two concurrent transactions trying to insert data in the User Table. Both transactions query the data in table to check if the new Username to insert does not appear in the table. The problem is that let say. Transaction1 and Transaction2 read User Table and found that there is no username embarus in User Table. Then Transaction2 trying to insert embarus in User table while Transaction1 has been inserted and committed embarus in table already.

Therefore Transaction2 get exception for unique constrain.

Please help me to solve this problem, any ideas or article that may be useful.

I found that SqlServer 2008 uses ReadCommitted for default transaction isolation level.

Thank you so much.

Upvotes: 0

Views: 453

Answers (1)

Jamie Ide
Jamie Ide

Reputation: 49251

You need to catch and handle the unique constraint violation. The best way to do that is to create an ISqlExceptionConverter implementation to translate the RDBMS specific exception to a custom exception in your application.

public class SqlServerExceptionConverter : ISQLExceptionConverter
{
    public Exception Convert(AdoExceptionContextInfo adoExceptionContextInfo)
    {
        var sqlException = adoExceptionContextInfo.SqlException as SqlException;
        if (sqlException != null)
        {
            // 2601 is unique key, 2627 is unique index; same thing: 
            // http://blog.sqlauthority.com/2007/04/26/sql-server-difference-between-unique-index-vs-unique-constraint/
            if (sqlException.Number == 2601 || sqlException.Number == 2627)
            {
                return new UniqueKeyException(sqlException.Message, sqlException);
            }
        }
        return adoExceptionContextInfo.SqlException;
    }
}

public class UniqueKeyException : Exception
{
    public UniqueKeyException(string message, Exception innerException)
        : base(message, innerException)
    { }
}

Usage:

            using (var txn = _session.BeginTransaction())
            {
                try
                {
                    var user= new User
                        {
                            Name = "embarus"
                        };
                    _session.Save(user);
                    txn.Commit();
                }
                catch (UniqueKeyException)
                {
                    txn.Rollback();
                    var msg = string.Format("A user named '{0}' already exists, please enter a different name or cancel.", "embarus");
                    // Do something useful
                }
                catch (Exception ex)
                {
                    if (txn.IsActive)
                    {
                        txn.Rollback();
                    }
                    throw;
                }
            }

Note that you should not reuse the session after the exception occurs.

Upvotes: 1

Related Questions