none
What transaction level should I use for... (EntityFrameworks)? RRS feed

  • Question

  • Hi;

    I'm using EntityFrameworks for the first time. Question on transactions. What transaction level should I use and how should I structure the code for:

    1. Reading a single record and I may update that record. This record is independent of all other data in the database.
    2. Trying to read a single record. If no record exists, then create a record with that value in that table. This is independent of all other tables, but it needs to lock this table so another thread doesn't also find no record, and then add the same record.

    ??? - thanks - dave


    Who will win The Windward International Collegiate Programming Championships?

    Tuesday, October 15, 2013 8:34 PM

Answers

All replies

  • Hello,

    For the Entity Framework translation, in my opinion, if the application is used by only one people, then I think we do not need to add additional translation for Entity Framework. Entity Framework has a inside translation.

    For entities that might experience a high degree of concurrency, we recommend that the entity define a property in the conceptual layer with an attribute of ConcurrencyMode="fixed", as shown in the following example:

    <Property Name="Status" Type="Byte" Nullable="false" ConcurrencyMode="Fixed" />

    More information regarding Entity Framework translation:

    http://msdn.microsoft.com/en-us/library/vstudio/bb896325(v=vs.100).aspx

    More information regards Entity Framework concurrency:

    http://msdn.microsoft.com/en-us/library/vstudio/bb738618(v=vs.100).aspx

    If I have misunderstood, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 16, 2013 6:34 AM
    Moderator
  • Hi Fred;

    Thank you for the links. They do a good job describing transactions in general. Unfortunately they do not answer my specific questions. My database will be updated by multiple threads concurrently so I do need to handle concurrency. 

    Can you please tell me how I should setup up transactions in EFs for:

    1. Reading a single record and I may update that record. This record is independent of all other data in the database.
    2. Trying to read a single record. If no record exists, then create a record with that value in that table. This is independent of all other tables, but it needs to lock this table so another thread doesn't also find no record, and then add the same record.

    thanks - dave


    Who will win The Windward International Collegiate Programming Championships?

    Wednesday, October 16, 2013 11:01 PM
  • >>Can you please tell me how I should setup up transactions in EFs.

    Maybe you can use the "TransactionScope" as the sample in the link:

    http://msdn.microsoft.com/en-us/library/vstudio/bb738523(v=vs.100).aspx

    Wednesday, October 23, 2013 5:42 AM
  • I am using TransactionScopes. The question is, what IsolationLevel should I use for each case?

    thanks - dave


    Who will win The Windward International Collegiate Programming Championships?

    Wednesday, October 23, 2013 11:36 AM
  • Hi,

    In my opinion, if the program exists conflict, I recommend that we use the serializable level.

    And for a program which will be used by only one user, I think we do not need to use TransactionScopes(), because the Entity Framework already has a inside transaction.

    >>Reading a single record and I may update that record. This record is independent of all other data in the database.

    If this is a stand-alone program, we just need to use the inside transaction.

    >>Trying to read a single record. If no record exists, then create a record with that value in that table. This is independent of all other tables, but it needs to lock this table so another thread doesn't also find no record, and then add the same record.

    Obviously, this is a multi-user program, so I recommend to use the serializable.

    If I have misunderstood, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 24, 2013 7:24 AM
    Moderator
  • Hi Fred;

    In both cases it is a multi-user program.

    My question is I want to lock as little as possible. As I understand it serializable basically locks everything and I do not want to do that as it will then make all database access sequential and that will be very slow when I have multiple threads hitting it.

    How can I lock just the access I need?

    thanks - dave


    Who will win The Windward International Collegiate Programming Championships?

    Thursday, October 24, 2013 12:32 PM
  • Please have a look at the IsolationLevel Enumeration, it specifies the isolation level of a transaction.

    You can choice a suitable level for the actual environment.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 25, 2013 9:36 AM
    Moderator
  • Fred;

    This is getting frustrating. No the enumerations do not list a suitable choice! Can you please ask someone who knows this well rather than continue to point me to documentation that does not answer my question?

    thanks - dave


    Who will win The Windward International Collegiate Programming Championships?

    Friday, October 25, 2013 1:25 PM
  • Hi DavidThi808,

    >>No the enumerations do not list a suitable choice!

    It is my mistake. I should give codes as below:

     TransactionOptions options = new TransactionOptions
                {
                    IsolationLevel = IsolationLevel.ReadCommitted
                };
                using (var transaction = new TransactionScope(TransactionScopeOption.Required, options))
                {
    
    
    		//...do some codes
    
                    transaction.Complete();
                }
    
    

    In the TransactionOptions, we could specifies the isolation level of a transaction.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 28, 2013 3:57 AM
    Moderator
  • ReadCommitted does not protect for this as documented here.

    Again, what isolation level will provide this protection?


    Who will win The Windward International Collegiate Programming Championships?

    Monday, October 28, 2013 2:23 PM
  • In my opinion, RepeatableRead will be suitable and protection.

    Use it like:

    TransactionOptions options = new TransactionOptions
                {
                    IsolationLevel = IsolationLevel.RepeatableRead
                };
                using (var transaction = new TransactionScope(TransactionScopeOption.Required, options))
                {
    
    
    		//...do some codes
    
                    transaction.Complete();
                }
    


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 29, 2013 10:13 AM
    Moderator
  • Wrong again. In RepeatableRead new rows can be added during the transaction.

    Can you please ask someone in the EF group who actually knows this stuff for an answer?

    thanks - dave


    Who will win The Windward International Collegiate Programming Championships?

    Tuesday, October 29, 2013 12:31 PM
  • >>Reading a single record and I may update that record. This record is independent of all other data in the database.

    Do you mean that in this transaction you do not want other user to modify/query the single record or modify/query the table the singel record in.

    If it is before, it should use the Row-level locking. And it maps to ReadUncommitted.

    If it is after, it should use HOLDLOCK.And it maps to Serializable.

    Regards.

    Wednesday, October 30, 2013 10:17 AM
  • Hi;

    No, my question is for the other case I listed at the top:

    Trying to read a single record. If no record exists, then create a record with that value in that table. This is independent of all other tables, but it needs to lock this table so another thread doesn't also find no record, and then add the same record.

    What locking should I do to insure that no records are added to the table while I have it locked.

    ??? - thanks - dave


    Who will win The Windward International Collegiate Programming Championships?

    Wednesday, October 30, 2013 12:48 PM
  • Hi;

    No, my question is for the other case I listed at the top:

    Trying to read a single record. If no record exists, then create a record with that value in that table. This is independent of all other tables, but it needs to lock this table so another thread doesn't also find no record, and then add the same record.

    What locking should I do to insure that no records are added to the table while I have it locked.

    ??? - thanks - dave


    There is no isolation level that has those semantics.  The most restrictive isolation level for SQL Server is SERIALIZABLE.  It will take range locks when querying for non-existent data, and hold them until the end of your transaction.  But those range locks are not exclusive; they are shared.  So when multiple sessions look for the row concurrently they may both see the empty range, both take a shared lock on it and then both try to insert. 

    This will cause a deadlock since each session owns a lock the other session needs, and one of the requests will fail.

    In this scenario it's better to leave your transaction isolation level at the default (READ COMMTTED), and use a stored procedure or a TSQL query to get the desired lock semantics.

    The correct locking semantics here are to use a restrictive range lock on the initial read, and hold that for the duration of the transaction.  In SQL Server that corresponds to the lock hints (UPDLOCK,SERIALIZABLE), or the equivalent (UPDLOCK,HOLDLOCK).  This causes SQL Server to use the same range locking that is used for the SERIALIZABLE, but the locks are U locks instead of S locks.  That way when multiple sessions attempt the read, only the first one will be able to read the key range, and others will be blocked until the first session finishes the insert and commits.

    You can use DbContext.Database.SqlQuery<T> to issue the raw TSQL with the lock hints.  Then if you get no results back, you can be sure that no other session will be able to insert that row before you.

    You can also consider enforcing the uniqueness with a key in SQL Server and handling the key violation exception when a session attempts to insert a duplicate.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Wednesday, October 30, 2013 5:04 PM
  • Hi;

    If I use a stored procedure don't I still face the same issue, that I can't lock the table while I do the query, and then the insert if the query does not return a row?

    This has got to be a common need. Ant DB handling membership faces this where it looks for a user and if the user does not exist, creates it. But needs to make sure another thread is not creating a user with the same name at the same time.

    ??? - thanks - dave


    Who will win The Windward International Collegiate Programming Championships?

    Wednesday, October 30, 2013 5:09 PM
  • >If I use a stored procedure don't I still face the same issue

    Yes.  You still need the lock hints in a stored procedure.  EG:

    begin transaction
    
    if not exists 
    (
    	select * 
    	from test with (updlock,serializable) 
    	where ID = @id
    )
    begin
       insert into test(id,a) values (@id,1)
    end
    
    commit transaction
    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, October 30, 2013 5:26 PM
  • Does that transaction lock all updates to the table?

    thanks - dave


    Who will win The Windward International Collegiate Programming Championships?

    Wednesday, October 30, 2013 6:00 PM
  • >Does that transaction lock all updates to the table?

    No it only locks the range that you query for.  In this case a single key (if the row exists) or a range of keys (if the row does not exist).

    If you want to lock the whole table, you can use the (TABLOCKX) hint instead.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Wednesday, October 30, 2013 6:02 PM
  • Ok, thanks. Sounds like EntityFrameworks is just not up to uses like this so we'll go back to calling ADO.NET directly.

    Thank you for the help.

    - dave


    Who will win The Windward International Collegiate Programming Championships?

    • Marked as answer by DavidThi808 Wednesday, October 30, 2013 6:05 PM
    Wednesday, October 30, 2013 6:05 PM
  • >EntityFrameworks is just not up to uses like this

    Yes.  Entity Framework and ADO.NET both favor an optimistic concurrency model where you get an exception on conflict, instead of using locking to prevent conflicts.

    If you want pessimistic lock-based concurrency in situations like this you have to drop down below both EF and ADO.NET and use TSQL.

    Whether you use ADO.NET SqlCommand, or EF's SqlQuery<>() doesn't really make any difference.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, October 30, 2013 6:10 PM
  • I'm ok with an exception on conflict. Can EF be set up so that if I create 2 records within an exception, I'll get an exception on one when completing the transaction?

    My concern is 2 identical records are inserted with no error notification of any kind.

    ??? - thanks - dave


    Who will win The Windward International Collegiate Programming Championships?

    Wednesday, October 30, 2013 6:52 PM
  • >Can EF be set up so that if I create 2 records within an exception, I'll get an exception on one when completing the transaction?

    Sure.  Just create a unique index or unique constraint on your table.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, October 30, 2013 6:56 PM