SQL Server transactions - whole db locked ?

Question

Hi, I have a problem on specific SQL Server 2008 customer installation. I wrote the code below to simulate the problem which happens in more complex system. There are two connections (each one with own transaction) opened and each connection modifies a table. Modified tables do not relate to each other. On development platform and other existing customer installations the code works fine. Only at one specific customer we have a problem that the second update in nested transaction hangs. I could make a workaround by moving the first update after commit of nested transaction.

I assume in that specific installation the db is configured to lock down the whole db when a transaction is started. But using DBCC useroptions results in very similar output on systems where the code works and this one.

How can I identify what's wrong here ?

Here's DBCC useroptions output from the problematic DB (SQL Server 2008) and my simplified test code:

textsize    2147483647
language    Deutsch
dateformat  dmy
datefirst   1
lock_timeout    -1
quoted_identifier   SET
arithabort  SET
ansi_null_dflt_on   SET
ansi_warnings   SET
ansi_padding    SET
ansi_nulls  SET
concat_null_yields_null SET
isolation level read committed


DbCommand command1 =null, command2 = null;
try
{
   const string cs = "Provider=SQLOLEDB.1;...";

   // open command and a transaction with default isolation level
   command1 = DbAccessFactory.CreateInitialzedCommand("System.Data.OleDb", cs, true);

   // select something
   command1.CommandText = "select * from plannerOrderHeaders where ...";
   DataSet ds = BusinessCasesHelper.Fill(command1, null, "plannerOrderHeaders");

   // make some changes in the table
   ...

   // update the table in DB
   BusinessCasesHelper.Update(command1, ds, true);

   // open command and a transaction with default isolation level on the same CS as command1
   command2 = DbAccessFactory.CreateInitialzedCommand("System.Data.OleDb", cs, true);
   // select something 
   command2.CommandText = "select * from mdOmOrders where ...";
   ds = BusinessCasesHelper.Fill(command2, null, "mdOmOrders");

   // make some changes
   ...

   // update the db
   BusinessCasesHelper.Update(command2, ds, true);

   command2.Transaction.Commit();
   cmd2Commited = true;
   command1.Transaction.Commit();
}
catch (Exception e) {...}

Answer 1

And why do you use ""Provider=SQLOLEDB.1" to access MS SQL Server?
And why do you commit instead of closing and disposing?

I can only guess how the mentioned BusinessCasesHelper, DbAccessFactory, etc. are implemented.
But your question implies that your consider your snippet opening transaction inside another transaction in the same context (i.e. on one connection) while I see that they are probably opening two connections which are not being disposed.