0

I have a very important question which I need help with. I will try to strip the problem down to this example:

Having a wcf/web service implementation (but it could be any other module) as follows:

public class Svc: ISvc
{
   public void PerformSimpleOperations()
   {
      try
      {
          // insert in other tables here (calling stored procs)

          /* important line here */
          dbHandler.InsertRecordInSpecificTable(recordType1);

          // commit transaction (default isolation level)
          dbHandler.CommitTransactDB();
      }
      catch
      {
          dbHandler.RollbackTransactDB();
      }
   }
   public void PerformComplexOperations()
   {
      try
      {
          // some complex operations here involving db (calling stored procs)

          /* important lines here */
          dbHandler.InsertRecordInSpecificTable(recordBEGIN);    
          foreach (var recordType2 in arrayOfRecordsType2)
              dbHandler.InsertRecordInSpecificTable(recordType2);    
          dbHandler.InsertRecordInSpecificTable(recordEND);    

          // commit transaction (default isolation level)
          dbHandler.CommitTransactDB();
      }
      catch
      {
          dbHandler.RollbackTransactDB();
      }
   }
}

There are 2 different methods which insert records, transaction based (ADO.NET SQLTransaction with default isolation level), into one 'SpecificTable' in database (of course called at anytime by different threads).

As it can be seen in the example, first method just inserts simple records of 'type1', but the second method inserts a bulk of records of 'type2' starting with some 'BEGIN' & 'END' identifiers so that this bunch could be processed as a whole later on by another application.

Since all the records have auto-incremental ID, my intent is to have in this table in between 'BEGIN' & 'END' only records of 'type2'.

Question is: Would it be possible that in this table, in between 'BEGIN' & 'END' records, to have slipped records of 'type1'?

AFAIK the database should be locked during that transaction (default isolation level) and normally this behavior should not happen, so my implementation should be right. Is it correct or I miss something important here?

Thanks a lot!

Learner
  • 3,297
  • 4
  • 37
  • 62
  • Where are you initialising the transaction, if at all? – podiluska Sep 19 '12 at 10:28
  • @podiluska: This is small example, like pseudo-code, of course I initialize the dbHandler. DbHandler uses ADO.NET. 'BeginTransaction()' is used to initialize the transaction. Again, the isolation level is the default one. – Learner Sep 19 '12 at 10:35

2 Answers2

2

The default transaction isolation level is READ COMMITTED - ie - you can't read unmodified uncommitted data. This is not a table lock, nor does it ensure that only one operation can occur at a time - nor would you normally want it to.

Perhaps SERIALIZABLE is a better option for your code.

http://msdn.microsoft.com/en-us/library/ms173763.aspx

podiluska
  • 50,950
  • 7
  • 98
  • 104
  • So, actually I could have 'type1' records slipped in between 'type2' records (records having an autoincrementing ID) , right? I really think your answer is very valuable since I need to fix my implementation. – Learner Sep 19 '12 at 11:05
  • 1
    I don't see anything preventing it. – podiluska Sep 19 '12 at 11:26
  • Thanks a lot, will do some tests and check for both isolation levels – Learner Sep 19 '12 at 11:48
  • I saw this somewhere... <> But actually I would like to lock inserting while other transaction inserts. How can I achieve that? – Learner Sep 19 '12 at 12:12
2

You are leaving out all the important details...

All these would greatly influence the answer.

But more importantly, you are making a few very dangerous fallacies in your statements:

My intent is to have in this table in between 'BEGIN' & 'END' only records of 'type2'.

This statement doesn't make sense. It uses a concept that simply doesn't exists: 'records in table ... between'. Records in a table are not before, not after, not between any other records. The concept of order cannot be applied to tables, never ever. As a concept, order exists only in a result, ie. in the return of a SELECT, and is undetermined except an explicit ORDER BY clause is added. So you can rephrase your question as I want to retrieve all the records inserted by the PerformComplexOperation in one atomic return and the answer for that is use an appropriate key:

  • generate a key value for the call
  • each record inserted uses this key as a column value
  • retrieval adds an appropriate WHERE clause to filter only this key

Overall you simplified the problem beyond comprehension. Rather that post abstract question using concepts you don't really grok, post you actual data model schema and actual code, along with the problem you encounter.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Thanks for your suggestions. So, I am using SqlTransaction which my dbHandler is taking care of creating one with default isolation level. The records have an autoincrementing ID, so this column should be used for order concept. – Learner Sep 19 '12 at 10:57
  • Is there a way to lock somehow inserting records from one transaction if another one already started inserting records? I want to apologize... I am new to 'isolation levels' and stuff. – Learner Sep 19 '12 at 12:14
  • As I said, use an explicit key to differentiate the batches. Preventing concurrent inserts so you can achieve monotonous batches is stillborn. – Remus Rusanu Sep 19 '12 at 12:18
  • Oh, so you mean I should have another column in that table where I set an identifier for each batch, right? – Learner Sep 19 '12 at 12:26
  • I think I understand now what you mean, sorry for all this fuss and for misinterpreting your answer. I know now what I need to do. – Learner Sep 19 '12 at 12:37