SQL Server transactions - whole db locked?
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 开发者_如何学JAVAother. 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) {...}
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.
精彩评论