0

Due to connection pool reusing connection. I was told that I should be explicitly setting the isolation level before running my query.

The only way I found to set the isolation level is BeginTransaction(isolationLevel).

However I want to run a bunch inserts without having them wrapped inside a transaction (running them as auto commit transaction) to avoid deadlock. And i dont want to run them as Serializable or Read Uncommitted just because the previous connection was set to that level.

for example I want to run

command.CommandText = "INSERT INTO ....; INSERT INTO ....; INSERT INTO ....;"
command.ExecuteNoneQuery();

Whats the best way to do this?

Ilyes
  • 14,640
  • 4
  • 29
  • 55
Steve
  • 11,696
  • 7
  • 43
  • 81
  • I assume you're using SQL Server? –  Dec 13 '18 at 18:02
  • https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017 – Joe Phillips Dec 13 '18 at 18:04
  • @JoePhillips was hoping that theres something in the connection object i can do to avoid having to write that line before every query – Steve Dec 13 '18 at 18:06
  • @Steve It gets set for the entirety of the connection... which is forever, if you're pooling – Joe Phillips Dec 13 '18 at 18:06
  • @Igor I have a unique key defined on the table. If two users try to insert A and B where user1 inserts A first and user2 inserts B first, then it would deadlock if its wrapped inside a transaction where as auto commit transaction would throw dup key exception – Steve Dec 13 '18 at 18:07
  • See the marked duplicate, the 2nd answer (highest voted one) should answer your question. If that is not the case let me know and I will re-open. – Igor Dec 13 '18 at 18:10
  • @Igor kinda hacky way to do it. 7 years has passed i was hoping that theres a parameter i can use to avoid that and to avoid having to use T-SQL to set it in command text – Steve Dec 13 '18 at 18:13
  • You could start a bounty on the marked duplicate to see if someone else can offer some better insight but based on that question, and others that were marked as duplicate of it, it seems this is the only way to do this. – Igor Dec 13 '18 at 18:14
  • @Igor thanks. And you were saying that not wrapping the insert inside transaction would still result in deadlock. can you provide more insight to that ? – Steve Dec 13 '18 at 18:17
  • I am going to retract that comment, I do not have time to test it at the moment and I am starting to doubt if it is accurate. – Igor Dec 13 '18 at 18:20

0 Answers0