Problem using TransactionScope and table adapter

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(SqlExcep tion
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(Tds ParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlData Reader ds,
RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(Command Behavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBeh avior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbA syncResult
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(D bConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(D bConnection
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