1

I did some research and haven't found any explanation to this. So, If there's one out there, I'm sorry.

I am using TransactionScope to handle transactions with my SQL Server 2012 database. I'm also using Entity Framework.

The fact is that when I start a new transaction to insert a new record into my table, it locks the entire table, not just that row. So, if I run the Db.SaveChanges(), without committing it, and go to management studio and try to get the the already committed data from the same table, it hangs and return me no data.

What I would like in this scenario is to lock just the new row, not the entire table.

Is that possible?

Thank you in advance.

Marco Alves
  • 2,776
  • 3
  • 23
  • 33

1 Answers1

2

One thing to be very careful of when using TransactionScope is that it uses Serializable isolation level by default which can cause many locking issues in SQL Server. The default isolation level in SQL Server is Read Committed, so you should consider using that in any transactions that use TransactionScope. You can factor out a method that creates your default TransactionScope and always set to ReadCommitted by default (see Why is System.Transactions TransactionScope default Isolationlevel Serializable). Also ensure that you have a using block when using TransactionScope, to make sure that if errors occur with the transaction processing that the transaction is rolled back (http://msdn.microsoft.com/en-us/library/yh598w02.aspx).

By default, SQL Server uses a pessimistic concurrency model, which means that as DML commands are being processed (inserts, updates, deletes), it will acquire an exclusive lock on the data that is changing, which will prevent other updates or SELECTs from completing until those locks are released. The only way to release those locks is to commit or rollback the transaction. So if you have a transaction that is inserting data into a table, and you run a SELECT * FROM myTable before the insert has completed, then SQL Server will force your select to wait until the open transaction has been commit or rolled back before returning the results. Normally transactions should be small and fast, and you would not notice as much of an issue. Here is more info on isolation levels and locking (http://technet.microsoft.com/en-us/library/ms378149.aspx).

In your case, it sounds like you are debugging, and have hit a breakpoint in the code with the transaction open. For debugging purposes, you can add a nolock hint to your query, which would show the results of the data that has been committed, along with the insert which has not yet been committed. Because using nolock will return UN-committed data, be very careful about using this in any production environment. Here is an example of a query with a nolock hint.

SELECT * FROM myTable WITH(NOLOCK)

If you continue to run into locking issues outside of debugging, then you can also check out snapshot isolation (Great article by Kendra Little: http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/). There are some special considerations when using snapshot isolation, such as tempdb tuning.

Community
  • 1
  • 1
BateTech
  • 5,780
  • 3
  • 20
  • 31
  • I am already using ReadCommited the exactly way you've suggested. Is there anyway to lock just the row, not the entire table and execute the select * from myTable without getting the uncommitted data? Thank you! – Marco Alves Jan 27 '14 at 19:29
  • 1
    You may be able to try `select * from mytable where id < theLastIDBeforeTheInsertOfYourTrx`. Outside of that the only option I know of is snapshot isolation. More than likely, the entire table is not being locked, probably just an X lock on the PK of the table where data is being inserted. Check out the accepted answer here to see what locks are being issued from the open transaction: (http://stackoverflow.com/questions/7820907/how-to-find-out-what-table-a-page-lock-belongs-to). – BateTech Jan 27 '14 at 20:32