locked
help understanding EF , Transactions and Unit of Work RRS feed

  • Question

  • User1034446946 posted

    I am trying to understand transactions a little more, but I don't know how to test them, eg two people accessing the same records at the same time,so i have no realpractisein how they work.

    Now i use a unit of work pattern in my solution, and it has no reference to a transaction Scope, so does it use transactions? and if so how does it work?

    Now transactions as pessimitc concurrency, so are there any occations where i use both pessemtic and optomistic concurrency at the same time?

    Lets say I want to assign a database record to a user, not only to update the UI to let other employees know someone if using that record but also prevent some concurrncy issues

    do i:

    //Start a transaction
    //Get the records from the database
    //Add a navigation propert to assign the employee
    //save the changes

    now if thats correct, at the point of me selecting the records, can anyone else select them? or change them?

    if i am using a unit of work the transation points are removed does that still apply?

    Now lets say i want to run other operations, if I am using a unit of work, am i right in assuming the transaction is looked until IDispose has ben run, or the IOC finisheds?

    Any information would be appriciated.

    Sunday, March 25, 2018 9:03 PM

Answers

  • User475983607 posted

    I don't need to know or care if the user has walked away, its irrelevent to the process. I need to run a script and update the datebase when the page is first loaded, what happens after that is not important what is important is that no one else can access the records that have been selected. Which from my undertstanding is pressemistic concurrency and is what TransactionScope is supposed to offer.

    If I understand this requirement, you must write code, .NET and SQL, to accomplish this requirement. 

    TransactionScope simply marks a block of code,  the start and end, as a transaction.  The records are free for update once the transaction completes.   You cannot use TransactionScope to keep record(s) locked as the web request will end at some point and the changes will rollback since the Complete command has not been issued.  This assumes you wrap the TransactionScope in a using statement.  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 26, 2018 3:58 PM
  • User475983607 posted

    EnenDaveyBoy

    thats fine, but are the records locked for the time it is in the TransactionScope?

    Yes.

    A transaction is a group of SQL statements where every statement commits or the entire transaction is rolled back.  

    BEGIN TRANSACTION;

    DELETE FROM Table WHERE col1 = @aParam
    INSERT INTO Table (col1, col2) SELECT col1, col2 FROM Table2 WHERE RecordId = @Id;

    SELECT col1, col2
    FROM Table AS t with (nolock)
    JOIN Table2 AS with (nolock) t2 ON t.col2 = t2.col2;

    COMMIT;

    TransactionScope is C# syntax that wraps SQL code in a BEGIN TRANSACTION and COMMIT/ROLLBACK.  The syntax looks like this but does essentially the same thing as above.

    using (TransactionScope scope = new TransactionScope())
    {
       //Do ADO stuff
    // Delete from Table
    // INSERT INTO Table from Table2
    // Select table and join table 2 scope.Complete(); }

     EF creates a transaction as explained above so I'm not sure why you are investigating TransactionScope. 

    https://msdn.microsoft.com/en-us/library/dn456843(v=vs.113).aspx

    EnenDaveyBoy

    (and does that work for a select or just and update)

    Locking is up to you and how the DB is configured.  The first example above would result in a dirty read if the transaction rolled back.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 26, 2018 8:20 PM
  • User1120430333 posted

    thats fine, but are the records locked for the time it is in the TransactionScope? (and does that work for a select or just and update)

    https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-isolation-levels

    https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 26, 2018 8:32 PM
  • User475983607 posted

    thanks for the help so far, one last question, is there a way i can test a query when I havea transaction in place? so i can see what happens when multiple people are using the site? the problem being if i run a debugger i can only see one user instance.

    Add a Visual Studio load test project and create a test that targets your needs.

    https://docs.microsoft.com/en-us/vsts/load-test/getting-started-with-performance-testing?view=vsts

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 27, 2018 1:10 PM
  • User1120430333 posted

    thanks for the help so far, one last question, is there a way i can test a query when I havea transaction in place? so i can see what happens when multiple people are using the site? the problem being if i run a debugger i can only see one user instance.

    The way you can  it is make a classlib project call it FunctionalTests where the tests will be using EF for real against the database. You can create two tests methods in a test class to get the data and update the database running the test methods with MSTest to test concurrency.  You make up the user instance in the tests.

    EF has to be installed in the FunctionalTests  project,  and the connectionstring has to be copied to the app.config that you will create for the FunctionalTests  project., because that is where .NET will look for the connectionstring.

    The FunctionalTests  will reference the project where you have the CRUD logic using EF against the database, and you can test methods in the CRUD project from the FunctionalTests  project class methods.  Of course, you can debug, single step and all of that to determine what is happening. You can even test for a concurrency exception being thrown, because you are using n-unit or one of the other testing framework.

    I never test the database through user interfaces. I create a Functionaltest project and test database functionality for real to make sure things are solid before I even start coding for UI functionality using the database. I just hook things up because I already tested everything using the FunctionalTests project.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 27, 2018 8:41 PM

All replies

  • User475983607 posted

    Unit of work is a form of a transaction.   In SQL a transaction is group of SQL statements with a defined begin and end, where all statements must succeed otherwise the entire group is rolled back.

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql

    Having read your other posts, the issue you are facing has to do with the design.

    Sunday, March 25, 2018 10:35 PM
  • User1034446946 posted

    Thanks i just want to understand and have confidence with transactions, that link doesn't answer my concerns. primarily because I can't test them in a real enviroment to see the effects.

    Sunday, March 25, 2018 10:57 PM
  • User-707554951 posted

    Hi EnenDaveyBoy

    A transaction is a single unit of work which means either ALL or NONE. If a transaction is successful, all of the data operations are committed and become a durable part of the database. If a transaction encounters errors/exceptions and must be canceled or rolled back, then all of the data modifications/operations need to be removed.

    Example: Transfer money from Bank Account1 to Account2. It consists of two processes: Withdraw amount from Account1 and Deposit that amount to Account2.Transferring money will only be accurate and successful if both the processes are individually successful. If that is not happening, suppose process 1 succeeds but process 2 fails, then the money will be deducted from Account 1 but not deposited to Account 2. If that happens, it will be very bad and no one will accept it.

    For full understanding, you could read the articles below:

    https://www.c-sharpcorner.com/article/transaction-in-net/

    https://www.codeproject.com/Articles/522039/A-Beginners-Tutorial-for-Understanding-Transaction

    https://www.c-sharpcorner.com/article/transactions-in-Asp-Net/

    Best regards

    Cathy

    Monday, March 26, 2018 7:15 AM
  • User475983607 posted

    Thanks i just want to understand and have confidence with transactions, that link doesn't answer my concerns. primarily because I can't test them in a real enviroment to see the effects.

    Huh?  The official documentation is of no use because you can't test your code?

    Monday, March 26, 2018 11:08 AM
  • User1034446946 posted

    EnenDaveyBoy

    Thanks i just want to understand and have confidence with transactions, that link doesn't answer my concerns. primarily because I can't test them in a real enviroment to see the effects.

    Huh?  The official documentation is of no use because you can't test your code?

    Who said they are of no use? i said the don't answer my concerns.

    Monday, March 26, 2018 11:29 AM
  • User1034446946 posted

    Hi EnenDaveyBoy

    A transaction is a single unit of work which means either ALL or NONE. If a transaction is successful, all of the data operations are committed and become a durable part of the database. If a transaction encounters errors/exceptions and must be canceled or rolled back, then all of the data modifications/operations need to be removed.

    Example: Transfer money from Bank Account1 to Account2. It consists of two processes: Withdraw amount from Account1 and Deposit that amount to Account2.Transferring money will only be accurate and successful if both the processes are individually successful. If that is not happening, suppose process 1 succeeds but process 2 fails, then the money will be deducted from Account 1 but not deposited to Account 2. If that happens, it will be very bad and no one will accept it.

    For full understanding, you could read the articles below:

    https://www.c-sharpcorner.com/article/transaction-in-net/

    https://www.codeproject.com/Articles/522039/A-Beginners-Tutorial-for-Understanding-Transaction

    https://www.c-sharpcorner.com/article/transactions-in-Asp-Net/

    Best regards

    Cathy

    Thanks for the info, i understand the basics of what its supposed to do butif you take this statement:

    If that is not happening, suppose process 1 succeeds but process 2 fails, then the money will be deducted from Account 1 but not deposited to Account 2. If that happens, it will be very bad and no one will accept it.

    1 succeeds but process 2 fails

    what and where are the success and fails? is it talking about database operations updates/inserts, or are you talking scripts in the TransactionScope

    lets say by the time the above transaction started there was enough money in the account, does the transaction protect the money in account for this transfer as the TransactionScope would need to select other transaction records to calculate the balance?

    Monday, March 26, 2018 11:55 AM
  • User753101303 posted

    Hi,

    If using EF all changes done and saved using DbContext.SaveChanges are part of a single transaction so all changes are done successfully or no change at all is done. See https://msdn.microsoft.com/en-us/library/dn456843(v=vs.113).aspx

    You can add a "version" information to telp with detecting concurrency issues (there is no magic and the principle is to have a where clause with the pk and most if not all old values or this "version" information). This information is updated automatically whenever the row is updated so it allows to be sure the row was not changed by someone else when you are saving your updates.

    Try https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application which explain quite well all that in details...

    Edit: to simulate that you could have an EF app and update a row that you loaded using SQL Server Management too (or whatever for your db). You should then trigger a concurrency issue when saving the data from the app.

    Monday, March 26, 2018 12:02 PM
  • User1034446946 posted

    Hi,

    If using EF all changes done and saved using DbContext.SaveChanges are part of a single transaction so all changes are done successfully or no change at all is done. See https://msdn.microsoft.com/en-us/library/dn456843(v=vs.113).aspx

    You can add a "version" information to telp with detecting concurrency issues (there is no magic and the principle is to have a where clause with the pk and most if not all old values or this "version" information). This information is updated automatically whenever the row is updated so it allows to be sure the row was not changed by someone else when you are saving your updates.

    Try https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application which explain quite well all that in details...

    Edit: to simulate that you could have an EF app and update a row that you loaded using SQL Server Management too (or whatever for your db). You should then trigger a concurrency issue when saving the data from the app.

    Thanks the second link you provided about concurrency has this paragraph

    Pessimistic Concurrency (Locking)

    If your application does need to prevent accidental data loss in concurrency scenarios, one way to do that is to use database locks. This is called pessimistic concurrency. For example, before you read a row from a database, you request a lock for read-only or for update access. If you lock a row for update access, no other users are allowed to lock the row either for read-only or update access, because they would get a copy of data that's in the process of being changed. If you lock a row for read-only access, others can also lock it for read-only access but not for update.

    Managing locks has disadvantages. It can be complex to program. It requires significant database management resources, and it can cause performance problems as the number of users of an application increases. For these reasons, not all database management systems support pessimistic concurrency. The Entity Framework provides no built-in support for it, and this tutorial doesn't show you how to implement it.

    and its pessimistic concurrency I am interested in, which says its not supported in EF, so is TransactionScope the correct method to use pessimitic concurrency with the EF? or am i miss understanding TransactionScope?

    Monday, March 26, 2018 12:17 PM
  • User475983607 posted

    Who said they are of no use? i said the don't answer my concerns.

    What are your concerns that the official MS Transactions doc do not address?  

    now if thats correct, at the point of me selecting the records, can anyone else select them? or change them?

    As far as I call tell, a transaction will not solve this application requirement.  Nor will unit of work.  This is a design issue where you must write a bit of code to stop other users from updating the record.  The requirements are around this are not clear.

    if i am using a unit of work the transation points are removed does that still apply?

    Unit of work is a design pattern for managing DB connections.  Basically the pattern allows model changes in memory that are committed at a later time as a singe Unit.  This is a similar concept to an SQL transaction but it happens on the Web Server.  

    IMHO, you've complicated the design by wrapping EF, which is already a unit of work, with a custom unit of work.  It is not clear how your unit of work is designed.  

    Now lets say i want to run other operations, if I am using a unit of work, am i right in assuming the transaction is looked until IDispose has ben run, or the IOC finisheds?

    Unit of work exists in the Web Server's memory.  Your code will tell the Unit of Work to commit model changes.  Entity framework creates the SQL and submits the script to the SQL server wrapped in a transaction as explained the docs.  The previous linked SQL explain what to expect in a SQL transaction.  Locking level is dependant on your SQL configuration.

    In all versions of Entity Framework, whenever you execute SaveChanges() to insert, update or delete on the database the framework will wrap that operation in a transaction. This transaction lasts only long enough to execute the operation and then completes. When you execute another such operation a new transaction is started.

    IDispose removes the Unit of work from server memory. By the time iDispose runs, the SQL script has already run.  

    Can you explain the problem you are trying to solve?

    Monday, March 26, 2018 12:18 PM
  • User1034446946 posted

    EnenDaveyBoy

    Who said they are of no use? i said the don't answer my concerns.

    What are your concerns that the official MS Transactions doc do not address?  

    EnenDaveyBoy

    now if thats correct, at the point of me selecting the records, can anyone else select them? or change them?

    As far as I call tell, a transaction will not solve this application requirement.  Nor will unit of work.  This is a design issue where you must write a bit of code to stop other users from updating the record.  The requirements are around this are not clear.

    EnenDaveyBoy

    if i am using a unit of work the transation points are removed does that still apply?

    Unit of work is a design pattern for managing DB connections.  Basically the pattern allows model changes in memory that are committed at a later time as a singe Unit.  This is a similar concept to an SQL transaction but it happens on the Web Server.  

    IMHO, you've complicated the design by wrapping EF, which is already a unit of work, with a custom unit of work.  It is not clear how your unit of work is designed.  

    EnenDaveyBoy

    Now lets say i want to run other operations, if I am using a unit of work, am i right in assuming the transaction is looked until IDispose has ben run, or the IOC finisheds?

    Unit of work exists in the Web Server's memory.  Your code will tell the Unit of Work to commit model changes.  Entity framework creates the SQL and submits the script to the SQL server wrapped in a transaction as explained the docs.  The previous linked SQL explain what to expect in a SQL transaction.  Locking level is dependant on your SQL configuration.

    In all versions of Entity Framework, whenever you execute SaveChanges() to insert, update or delete on the database the framework will wrap that operation in a transaction. This transaction lasts only long enough to execute the operation and then completes. When you execute another such operation a new transaction is started.

    IDispose removes the Unit of work from server memory. By the time iDispose runs, the SQL script has already run.  

    Can you explain the problem you are trying to solve?

    I am just trying to figure you transactions and pessemistic concurrency.

    I think part of the problem is the wording for transactions, it carries across different processes.

    If i run TransactionScope, is there any point in running an optomistic concurrency check as well?

    Monday, March 26, 2018 1:36 PM
  • User475983607 posted

    and its pessimistic concurrency I am interested in, which says its not supported in EF, so is TransactionScope the correct method to use pessimitic concurrency with the EF? or am i miss understanding TransactionScope?

    Web applications are stateless and therefore Pessimistic concurrency does not work (well) in web applications.  There is no way to determine if the user has walked away from their desk which leaves the record locked for everyone else.

    As far as I can tell from your other threads, the design is based on calculated fields.  The design holds the current item inventory in a single record.  This record is recalculated every time inventory changes.  This design breaks the 3rd normalized form in DB design.  Frankly, the current issue you are facing is the textbook reason to not use calculated fields in a DB. The solution is using a query to calculate totals rather than locking a specific record.

    Anyway, this is a common programming problem in accounting applications.  This programming problem is solved by using standard account practices of Credit/Debits.  Each time inventory changes, a record is written to a table that documents the inventory transaction.  In DB terms this is a schema design where a table contains at least a Product ID, Date, Credit/Debit, and amount.  From there a simple GROUP BY query with addition/subtraction will produce current inventory totals. This completely removes the locking issue you're  currently fighting.  All that required is INSERTing a new record.

    Calculated field comes into play is when creating history records (partitioning the inventory table) as you'll need a starting balance at some defined point in time to keep accurate totals.  This process generally happens during application maintenance/downtime.  The frequency is up to you; daily, weekly, quarterly, yearly, etc and dependant on activelty vs performance.

    IMHO, the problem biggest problem with your current design is inventory reconciliation is near impossible.  Finding a human error and fixing the inventory totals is a lot of work.

    Monday, March 26, 2018 1:55 PM
  • User1034446946 posted
    Web applications are stateless and therefore Pessimistic concurrency does not work (well) in web applications.  There is no way to determine if the user has walked away from their desk which leaves the record locked for everyone else.

    I don't need to know or care if the user has walked away, its irrelevent to the process. I need to run a script and update the datebase when the page is first loaded, what happens after that is not important what is important is that no one else can access the records that have been selected. Which from my undertstanding is pressemistic concurrency and is what TransactionScope is supposed to offer.

    As far as I can tell from your other threads, the design is based on calculated fields.  The design holds the current item inventory in a single record.  This record is recalculated every time inventory changes.  This design breaks the 3rd normalized form in DB design.  Frankly, the current issue you are facing is the textbook reason to not use calculated fields in a DB. The solution is using a query to calculate totals rather than locking a specific record.

    This isn't the case. (in regards to my setup, not your solution)

    IMHO, the problem biggest problem with your current design is inventory reconciliation is near impossible.  Finding a human error and fixing the inventory totals is a lot of work.

    There is no human error.

    Monday, March 26, 2018 2:28 PM
  • User475983607 posted

    I don't need to know or care if the user has walked away, its irrelevent to the process. I need to run a script and update the datebase when the page is first loaded, what happens after that is not important what is important is that no one else can access the records that have been selected. Which from my undertstanding is pressemistic concurrency and is what TransactionScope is supposed to offer.

    If I understand this requirement, you must write code, .NET and SQL, to accomplish this requirement. 

    TransactionScope simply marks a block of code,  the start and end, as a transaction.  The records are free for update once the transaction completes.   You cannot use TransactionScope to keep record(s) locked as the web request will end at some point and the changes will rollback since the Complete command has not been issued.  This assumes you wrap the TransactionScope in a using statement.  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 26, 2018 3:58 PM
  • User1120430333 posted

    Maybe, you should post to the below forum too for help. IMHO, you could just toss UoW and the generic repository and go in another direction. 

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework

    Monday, March 26, 2018 4:17 PM
  • User1034446946 posted

    Maybe, you should post to the below forum too for help. IMHO, you could just toss UoW and the generic repository and go in another direction. 

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework

    at present i have no intension of using the UoW or generic repository for this.

    Monday, March 26, 2018 7:01 PM
  • User1034446946 posted

    If I understand this requirement, you must write code, .NET and SQL, to accomplish this requirement.

    Thats the plan.

    TransactionScope simply marks a block of code,  the start and end, as a transaction.  The records are free for update once the transaction completes.   You cannot use TransactionScope to keep record(s) locked as the web request will end at some point and the changes will rollback since the Complete command has not been issued.  This assumes you wrap the TransactionScope in a using statement.  

    thats fine, but are the records locked for the time it is in the TransactionScope? (and does that work for a select or just and update)

    I don't need them locked for the entire process, and if it rollsback because of any failer that fine its not as issue

    And therefore can I run a sql statement that looks up records that are not locked.

    Monday, March 26, 2018 7:27 PM
  • User475983607 posted

    EnenDaveyBoy

    thats fine, but are the records locked for the time it is in the TransactionScope?

    Yes.

    A transaction is a group of SQL statements where every statement commits or the entire transaction is rolled back.  

    BEGIN TRANSACTION;

    DELETE FROM Table WHERE col1 = @aParam
    INSERT INTO Table (col1, col2) SELECT col1, col2 FROM Table2 WHERE RecordId = @Id;

    SELECT col1, col2
    FROM Table AS t with (nolock)
    JOIN Table2 AS with (nolock) t2 ON t.col2 = t2.col2;

    COMMIT;

    TransactionScope is C# syntax that wraps SQL code in a BEGIN TRANSACTION and COMMIT/ROLLBACK.  The syntax looks like this but does essentially the same thing as above.

    using (TransactionScope scope = new TransactionScope())
    {
       //Do ADO stuff
    // Delete from Table
    // INSERT INTO Table from Table2
    // Select table and join table 2 scope.Complete(); }

     EF creates a transaction as explained above so I'm not sure why you are investigating TransactionScope. 

    https://msdn.microsoft.com/en-us/library/dn456843(v=vs.113).aspx

    EnenDaveyBoy

    (and does that work for a select or just and update)

    Locking is up to you and how the DB is configured.  The first example above would result in a dirty read if the transaction rolled back.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 26, 2018 8:20 PM
  • User1120430333 posted

    thats fine, but are the records locked for the time it is in the TransactionScope? (and does that work for a select or just and update)

    https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-isolation-levels

    https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 26, 2018 8:32 PM
  • User1034446946 posted

    thanks for the help so far, one last question, is there a way i can test a query when I havea transaction in place? so i can see what happens when multiple people are using the site? the problem being if i run a debugger i can only see one user instance.

    Tuesday, March 27, 2018 12:16 PM
  • User475983607 posted

    thanks for the help so far, one last question, is there a way i can test a query when I havea transaction in place? so i can see what happens when multiple people are using the site? the problem being if i run a debugger i can only see one user instance.

    Add a Visual Studio load test project and create a test that targets your needs.

    https://docs.microsoft.com/en-us/vsts/load-test/getting-started-with-performance-testing?view=vsts

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 27, 2018 1:10 PM
  • User1120430333 posted

    thanks for the help so far, one last question, is there a way i can test a query when I havea transaction in place? so i can see what happens when multiple people are using the site? the problem being if i run a debugger i can only see one user instance.

    The way you can  it is make a classlib project call it FunctionalTests where the tests will be using EF for real against the database. You can create two tests methods in a test class to get the data and update the database running the test methods with MSTest to test concurrency.  You make up the user instance in the tests.

    EF has to be installed in the FunctionalTests  project,  and the connectionstring has to be copied to the app.config that you will create for the FunctionalTests  project., because that is where .NET will look for the connectionstring.

    The FunctionalTests  will reference the project where you have the CRUD logic using EF against the database, and you can test methods in the CRUD project from the FunctionalTests  project class methods.  Of course, you can debug, single step and all of that to determine what is happening. You can even test for a concurrency exception being thrown, because you are using n-unit or one of the other testing framework.

    I never test the database through user interfaces. I create a Functionaltest project and test database functionality for real to make sure things are solid before I even start coding for UI functionality using the database. I just hook things up because I already tested everything using the FunctionalTests project.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 27, 2018 8:41 PM
  • User1034446946 posted

    Thanks for all the help.

    Thursday, April 5, 2018 1:43 AM