I have just setup a clone of our development database to use as a test environment. In the process I removed all data from it. Now when I run the code LLBLGen creates a deadlock and Im not sure what to do.

Info LLBLGen 2.6 Pro to generate the c# from the db. (runtime 2.6.09.0327) SQL Server 2008

The C# looks like:

TransactionRowCollection transactions = new TransactionRowCollection();
PredicateExpression depositFilter = new PredicateExpression();
// code to fill up the depositfilter removed here...
transactions.GetMulti(depositFilter);
//the transactions variable now contain 2 items
Transaction transactionManager = new Transaction(IsolationLevel.ReadCommitted, "SavepointRollback");
try
{
    foreach (TransactionRowEntity t in transactions)
    {
        StatusEntity newStatus = new StatusEntity { Status = (byte)status };
        t.StatusRows.Add(newStatus); // The 2nd time it comes here the deadlock happens

        transactionManager.Add(t);
        transactionManager.Add(newStatus);

        t.Save(true);
    }
    transactionManager.Commit();
    return true;
}
catch (Exception e)
{
    transactionManager.Rollback();
    Logger.Error(e);
}
finally
{
    transactionManager.Dispose();
}

When i traced this in the SQL Profiler the SQL that is run is this. It start with one connection that does this:

set transaction isolation level read committed
begin tran
INSERT INTO [Test].[dbo].[Status] ([TransactionID], [Status]) VALUES (6122481, 1)

Then in another connection it runs this:

set transaction isolation level read committed
begin tran
SELECT * FROM [Test].[dbo].[Status] with(rowlock) WHERE [Test].[dbo].[Status].[TransactionID] = 6122482

However, that SELECT statement will never complete because the INSERT has a lock..

In our production and development databases this works fine, I guess its because the Status table contains a lot more data (100000+ rows instead of less than 100) so SQL Server take different locks, but the question is what to do in our test environment where I dont want to have massive amounts of data in the db?

We don't generate SQL with '*' nor with a default lock, is it some other code which might execute that?

Anyway, why not do:

TransactionRowCollection transactions = new TransactionRowCollection();
PredicateExpression depositFilter = new PredicateExpression();
// code to fill up the depositfilter removed here...
transactions.GetMulti(depositFilter);
//the transactions variable now contain 2 items
try
{
    foreach (TransactionRowEntity t in transactions)
    {
        StatusEntity newStatus = new StatusEntity { Status = (byte)status };
        t.StatusRows.Add(newStatus); // The 2nd time it comes here the deadlock happens
    }
    transactions.SaveMulti(true);
    return true;
}
catch (Exception e)
{
    Logger.Error(e);
}

?

This will start a transaction automatically and will access the DB in 1 go, so the transaction is shorter.

Oh, sorry. I shortened the SQL to make it readable while I tested and recreated the problem manually in Management Studio.

The full SQL that was run for the SELECT was:

exec sp_executesql N'SELECT [Test].[dbo].[Status].[StatusID] AS [StatusId], [Test].[dbo].[Status].[TransactionID] AS [TransactionId], [Test].[dbo].[Status].[Status], [Test].[dbo].[Status].[Created], [Test].[dbo].[Status].[Description] FROM [Test].[dbo].[Status]  WHERE ( ( [Test].[dbo].[Status].[TransactionID] = @TransactionId1))',N'@TransactionId1 int',@TransactionId1=6122482

And then the INSERT:

declare @p3 int
set @p3=NULL
exec sp_executesql N'INSERT INTO [Test].[dbo].[Status] ([TransactionID], [Status])  VALUES (@TransactionId, @Status);SELECT @StatusId=SCOPE_IDENTITY()',N'@StatusId int output,@TransactionId int,@Status tinyint',@StatusId=@p3 output,@TransactionId=6122481,@Status=1
select @p3

Apparently the problem goes away when there's a lot of rows in the Status table, I ran the insert a 1000 times and after that the problem went away.

About your code suggestion: I have no idea I have only worked on this code base (and with LLBLGen) for a couple of months and the code is several years old with the original developer gone. But you are right, looks like a good rewrite that I will try.

Edit: Yep, with the rewrite the error goes away. Thanks for the help.