transaction management

[Ross Owen <rl...@firstam.com>]

We are having a major problem with transactions.  We keep getting
locking.  We have an ASP.NET 2.0 Web application and was encouraged to
use iBatis.  We are trying to release into production but we are failing
on our stress testing miserably.  The only way we can stop the locking
is to use IsolationLevel.Serializable which we don't want to do.  We are
only using SQLServer2005 with a single connection string.  The only
other sql going on is with Microsoft's ASP.NET MembershipSecurity stuff.


 

PLEASE HELP.  I am desperate.

 

Here are some of the things we have set up.  

 

public class SqlMapperFactory

{

    private static volatile SqlMapper _mapper = null;

 

    static SqlMapperFactory()

    {

        DomSqlMapBuilder builder = new DomSqlMapBuilder();

        XmlDocument sqlMapConfig =
Resources.GetEmbeddedResourceAsXmlDocument("Config.sqlMap.config,Persist
ence");

        _mapper = builder.Configure(sqlMapConfig);

    }

 

    public static SqlMapper GetMapper()

    {

        return _mapper;

    }

}

 

public class SqlMapperWrapper : ISqlMapper

{

    private SqlMapper _sqlMapper;

    private int _transactionCount = 0;

 

    public SqlMapperWrapper(SqlMapper sqlMapper)

    {

        _sqlMapper = sqlMapper;

    }

 

    public SqlMapperWrapper()

    {

        EventLog log = new EventLog();

        log.Source = "NotificationWS";

        log.Log = "Application";

 

        try

        {

            new IdRequest();

            _sqlMapper = SqlMapperFactory.GetMapper();

        }

        catch (Exception e)

        {

            log.WriteEntry(e.ToString(),
EventLogEntryType.FailureAudit);

        }

    }

 

    public object QueryForObject(string statementName, object parameter)

    {

        return _sqlMapper.QueryForObject(statementName, parameter);

    }

 

    public object Insert(string statementName, object parameter)

    {

        return _sqlMapper.Insert(statementName, parameter);

    }

 

    public IList QueryForList(string statementName, object parameter)

    {

        return _sqlMapper.QueryForList(statementName, parameter);

    }

 

    public int Update(string statementName, object parameter)

    {

        return _sqlMapper.Update(statementName, parameter);

    }

 

    public int Delete(string statementName, object parameter)

    {

        return _sqlMapper.Delete(statementName, parameter);

    }

 

    /// <summary>

    /// Only creates a transaction if one has not already been started

    /// </summary>

    public void BeginTransaction()

    {

        _transactionCount++;

        if (!_sqlMapper.IsSessionStarted)

        {

            _sqlMapper.BeginTransaction(IsolationLevel.Serializable);

        }

    }

 

    public void CommitTransaction()

    {

        _transactionCount--;

        if (_transactionCount == 0)

        {

            _sqlMapper.CommitTransaction();

        }

    }

 

    public void RollbackTransaction()

    {

        _transactionCount--;

        if (_transactionCount == 0)

        {

            _sqlMapper.RollBackTransaction();

        }

    }

 

    public void RollbackTransaction(Exception e, string location)

    {

        RollbackTransaction();

        if (e.GetType() == typeof (DBConcurrencyException))

        {

            throw e;

        }

        else

        {

            throw new
TransactionRollbackException(string.Format(ErrorText.Rollback,
location), e);

        }

    }

}