0

I have created a demo application to understand the C# SqlTransaction.

I have a table Region, and I have developed a Windows Forms application that starts two threads:

THREAD 1:

  1. Start a transaction.
  2. Insert a record.
  3. Sleep for 60 seconds
  4. Commit the transaction.

THREAD 2

  1. Start the transaction.
  2. Update any old record
  3. Commit the transaction.

Here because thread one's transaction is sleeping fro 60 seconds, thread two's transaction have to wait.

Is there any other way or isolation level by which I can do this INSERT and UPDATE together?

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
Yash
  • 356
  • 1
  • 5
  • 22
  • 5
    Snapshot isolation would _generally_ allow this. Can I suggest though that sleeping for 60 seconds in a transaction is a **terrible** idea? – mjwills Jul 01 '19 at 10:38
  • 2
    Row locks ensure that *generally*, transactions do not need to wait on each other if they touch *different* rows and the queries and indexing are aligned to allow it. In other words, if the `UPDATE` is written in such a way that the engine can see it will not involve the new row, and it does not update so many rows that locks escalate, it will not be blocked. – Jeroen Mostert Jul 01 '19 at 10:44
  • @mjwills Thanks for the comment. This is my demo application and wanted test uncommitted transaction. That is why I have use sleep 60 sec before committee. Snapshot Isolation worked for me. But by doing some google search, I found that it can decrease performance of my database if I enable it. Is that true? Is there any other drawback? – Yash Jul 01 '19 at 12:28

0 Answers0