0

I'm currently doing the following in a ReadUncommitted transaction on mulitple thread, in a an old system:

  1. Each thread get a unique set of data to work on. There are never duplicates across these threads.
  2. The threads gather data from a bunch of tables (only reads) for their unique data set.
  3. Based on the gathered data each thread create n number of new entities, Emails, in memory for later writing.
  4. Then for each email entity the threads have to check if the email entity already exists in the table, to avoid duplicates. This involves comparing x number of columns.
  5. Afterwards the threads write the email entities which do not already exist, knowing that they will be clean inserts ALWAYS as the threads and this application are the only ones who can write these type of email entities and the threads always work on unique data sets.
  6. Then the threads commit.

Now I KNOW this is not a pretty design, but it's to big a task to refactor the entire system. The reason for the threading is purely to gain better performance.

My question is:

  • Are there any dangers in using the ReadUncommitted isolationlevel in my case?

I've been reading up on isolation levels again and again, lately, just to make sure, but I'm not that experienced with SQL Server yet.

Edit: I should mention that the reason for switching to using the ReadUncommitted isolation level was that I wanted the threads to be able to commit simultaneously, for optimal performance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Christian Mikkelsen
  • 1,661
  • 2
  • 19
  • 44
  • 1
    ReadUncommitted has no effect on updates or inserts. It just signals that you are ok with READING potentially incomplete or partial results. – Andomar Feb 05 '13 at 20:15
  • If the thread is performing inserts to multiple tables in the same transaction then some performance could be gained. This way you wouldn't hold a lock on the first table while inserting into the rest of the tables so you could get shorter locking at the table level. – Eric J. Price Feb 05 '13 at 20:31
  • According to http://msdn.microsoft.com/en-us/library/system.data.isolationlevel(v=vs.110).aspx, "A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored." Which i interpret as there is no exclusive lock on the table i write to. Am i missing something? – Christian Mikkelsen Feb 05 '13 at 20:31
  • You have to lock a table to write to it, someone else can read a dirty version of the table while you're inserting, but multiple inserts can't happen at the same time. – Eric J. Price Feb 05 '13 at 20:32
  • @Love2Learn: Okay this explains why i would get lock errors if not using a ReadUncommitted transaction as the dublicate check would need to read the table while another thread might writting to it. Does that sound correct? – Christian Mikkelsen Feb 05 '13 at 20:32
  • The quote you posted is only in relation to "reads" as is designated by "dirty read" term that sets the subject for the rest of the comment. Everything stated after that point is in relation to reads. – Eric J. Price Feb 05 '13 at 20:34
  • So the transacion isloation level ONLY involves reads and have no effect on writes? – Christian Mikkelsen Feb 05 '13 at 20:35

0 Answers0