Situation: WCF Service with TransactionScopeRequired OperationBehaviour. WCF Service runs a process that used SqlBulkCopy to insert many rows to a db.
When used without the TransactionScopeRequired behaviour, the SqlBulkCopy inserts records with no issue, however when this behaviour is set to true, not even a small amount of records can be inserted.
Further info - The WCF Service uses a MSMQ Endpoint.
ok.. It was late when I posted the above..:)
Following further analysis (I am ashamed to be writing this..!) the issue was due to the implementation of my service making use of the Task Processing Library - i.e. I had multiple processes making DB calls wrapped within a TransactionScope - for example -
-First process performs actions against DB. This action appends a transaction to the transactionscopes transaction and caused a db lock.
-Second process is then spawned and attempts to write to the same table - it is unable to as the transaction is not part of the original transaction scope.
Solution - Some redesign will be required to provide smarter transaction management!