2

I am working on a custom application that allows users to insert / update / delete / retrieve data from a database.

Stored procedures I use affects a few tables so I use a transaction and I want to be able to lock these tables to prevent unintended things from happening when multiple users are inserting , deleting, updating, or retrieving (CRUD) data.

My question is what would be the best isolation level to use here, I have read the MSDN on isolation levels and tried to make sense of it as well as searching around, If someone who has been here done that could comment quickly that would be greatly appreciated.

1 Answers1

1

The default .Net transaction level is serializable. Why do you need another transaction level? Are you under the impression that without an explicit transaction, other connections will be able to change the data out from under you? If so, that is incorrect -- all actions take place in an explicit or implicit transaction, and the only time you can get inconsistent results is if you explicitly set the transaction isolation level to something which allows it.

Edit: As pointed out in a comment, the default for the database engine is READ COMMITTED, but the default for the .Net transactions/connections is SERIALIZABLE, with a caveat -- if the transaction level is changed, it will not be "dropped" for a pooled connection. This means that you can never be absolutely sure what it is unless you set it yourslf. For most cases, you can probably get away with assuming SERIALIZABLE and leave it at that.

Community
  • 1
  • 1
jmoreno
  • 12,752
  • 4
  • 60
  • 91
  • -1 [READ COMMITTED is the default isolation level for the Microsoft SQL Server Database Engine.](http://stackoverflow.com/questions/10003026/what-is-the-default-transaction-isolation-level-for-sql-server-with-ado-net), not SERIALIZABLE. – Vladimir Baranov Nov 24 '15 at 06:38
  • @VladimirBaranov: that is the default DATABASE isolation level, I believe it is different for .net/ado/entity framework. – jmoreno Nov 24 '15 at 06:46
  • Oops, I didn't think about that. Unfortunately, I can't undo downvote, unless the answer is edited. You can add a link from my previous comment to the answer or edit it in some other way and I should be able to revert the downvote. – Vladimir Baranov Nov 24 '15 at 07:07
  • @VladimirBaranov: I have editted my answer to make it clearer that I was talking about the .net default, your link about the database default and a link explaining the .net default (and a caveat that isn't covered by any of the links). – jmoreno Nov 24 '15 at 07:35
  • I can tell that you know a lot about transactions but the question is unanswerable without a lot of details about the workload and requirements. – usr Nov 24 '15 at 11:07
  • @usr: answering to the level that the OP could just do whatever the answer says and not have to consider anything else, would require a lot more details. But the answer doesn't need all that context to be useful, and as I said, the most likely solution is to just use the defaults. Needing something else is rare. – jmoreno Nov 24 '15 at 15:33