0

I have a function that is being called recursively. In this function, a TransactionScope object with Isolation level ReadUncommitted is being used. The strange behaviour is that the first time, it hits Database, locks the table and after the completion of first iteration, it releases the table and the behaviour is fine. Can anyone explain why? Please note that I'm using Core ADO.Net. No ORM. My functon looks like the following:

public void ExportTransactionsData()
{
    var now = DateTime.Now;
    var slash = ConfigurationReader.PathCharacterToUse;
    var startId = 0L;
    var endId = 0L;
    var hasMoreRecords = false;

    using (var transScope = UtilityMethods.TransactionScope(IsolationLevel.ReadUncommitted, TransactionScopeOption.Required))
    {
        using (var stream = _provider.GetTransactionsXml(out startId, out endId, out hasMoreRecords))
        {
            if (stream.Length > 0)
            {
                var filePath = $"{ConfigurationReader.TransactionFilePath}{slash}EZFareTransactions_{startId}_{endId}_{now.ToString("yyyyMMddTHHmmss")}.xml";

                using (IUploadClient client = UploadClientFactory.Create(ConfigurationReader.UploadType))
                {
                    client.Upload(filePath, stream);                            
                }
            }
        }

        transScope.Complete();
    }

    if (hasMoreRecords)
    {
        ExportTransactionsData();
    }
}
Muhammad Qasim
  • 1,622
  • 14
  • 26
  • As an aside, why wouldn't you use a `do ... while` loop here instead of recursion? Without a guarantee that tail calls are optimized, this runs a completely unnecessary risk of a stack overflow. – Jeroen Mostert Dec 13 '18 at 15:38
  • This snippet also contains no code that actually accesses the database, which would be necessary to see what's happening in terms of locking. (No, using `ReadUncommitted` does not mean no locks are ever taken.) What's in `GetTransactionsXml`? – Jeroen Mostert Dec 13 '18 at 15:44
  • `GetTransactionsXml` is simply calling a procedure that returns data in form of xml using `ADO.Net` and using XMLReader, its saving xml into a stream – Muhammad Qasim Dec 13 '18 at 16:15
  • You seem to be assuming the `TransactionScope` is the only interesting bit, possibly combined with recursion. It's not. Open up the box, look in the procedure. How are you establishing this code is "locking the table", and that only the first time? What do you observe? A table lock is a big thing that statements rarely actually take, but if they do they're typically schema stability locks, which in turn rarely block other statements. – Jeroen Mostert Dec 13 '18 at 16:20
  • I will say that getting `stream.Length` is suspect. Most streams don't know their length; those that do may be inappropriately loading a lot of data in memory in advance -- that might also cause observable delays. Moreover, if the data in your stream *is* read in advance, it makes little sense to put the upload of the data inside the transaction. In fact, given that you're apparently reading data, using a transaction at all is suspect. – Jeroen Mostert Dec 13 '18 at 16:24
  • With `ReadUncommitted` there's an additional complication: the isolation level is not reset when the connection is returned to the pool, so this connection will henceforth act under `ReadUncommitted` (which is not the default). Make sure to either explicitly set and reset isolation level, or else use `TransactionScope` consistently for all access. If this bit of code is the only database access you're doing, there's no issue, but otherwise it's something to keep in mind. – Jeroen Mostert Dec 13 '18 at 16:25

0 Answers0