On TransactionScope and TableAdapter

[Guest]

Hi,

We are developing a WCF service using .NET 3.0.

The target platform : Win XP,SP 2.0

In the data access layer, we use ADO .NET to connect to the SQL Express
database.Data access layer is implemented as typed datasets with tableadapter
for each table.The service implementation at the business logic layer uses
TransactionScope as shown below.

void MyService()
{

....

using (TransactionScope scope = new TransactionScope())
{

using(Tableadapter1)
{
using(TableAdapter2)
{
}

}
scope.Complete();
}

}
All the table adapters are from the same database.Multiple service
methods having similar implementation ( uses more or less the same table
adapters ) can be called by the different clients simultaneously.

(1) We are getting Transaction deadlock.Pasted below is the detailed error
message.We have gone thorough the order in which the tables are accessed.Its
more or less the same in different methods.How do we eleminate or minimise
these deadlocks ?

System.Data.SqlClient.SqlException: Transaction (Process ID 69) was
deadlocked on lock resources with another process and has been chosen as the
deadlock victim. Rerun the transaction. at
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

(2)Some time the same set of operations give the Timeout error with having
used the max connection pool.How do we change the default timeout period /
pool size ?

System.InvalidOperationException: Timeout expired. The timeout period
elapsed prior to obtaining a connection from the pool. This may have
occurred because all pooled connections were in use and max pool size was
reached.
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32
maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32
startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)


With Regards
Ajit