Results 1 to 10 of 10

Thread: I want a concurrency violation and am not getting one

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Working from home, social distancing, just like you
    Posts
    4,166

    I want a concurrency violation and am not getting one

    I thought that if you had a record open with outstanding changes and someone else updated that record and then you went to save your record, you would get a concurrency violation.

    What is happening is I am simulating a scenario where a user has a case open and someone else has a different case open which of course is fine. But someone-else links the cases so a-user's case has its relProjectControl updated through an update query in a table adapter (UPDATE Jobs SET relProjectControl = @newProjectControl WHERE(Control = @control)). Both the cases are now 813 at the database. But not in a-user's memory. So when he closes and saves the case, his relProjectControl is updated back to 0. This is a silent error (or issue, because it doesn't look like it is an error) that no one will know happened. The cases that were just linked are not linked any longer. Why wasn't there an error?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Working from home, social distancing, just like you
    Posts
    4,166

    Re: I want a concurrency violation and am not getting one

    Please disregard this until I post back that I know what i'm talking about...
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,562

    Re: I want a concurrency violation and am not getting one

    I know you said to disregard it while you researched but I can probably provide a shortcut and it might be useful to others too.

    What you're talking about is "pessimistic concurrency". It's pessimistic because it assumes collisions will occur so tries to prevent them whereas optimistic concurrency assumes they won't so just tries to fix them when they do.

    So the way pessimistic concurrency works is that, when a record is being read, a "shared" lock is applied to that record. When a record is being updated an "exclusive" lock is applied. There can be an unlimited number of shared locks on a record at a time but an exclusive lock must be the only lock a record. So the effect is that multiple processes can read a record at a time but an updated cannot happen while any other process is either reading or updating that record. And once an update starts, nothing can read or update that record until the first update has completed.

    The reason I bolded "is being read" is that I think that's the bit you're probably missing. Your update would have failed (or at least been delayed) if the record was actually still being read. But what's happened in your case is that your application has already read the record, released it's shared lock and displayed it on screen. It will have released it's shared lock as soon as it finished reading the record, which is considerably earlier than your user stopping viewing it.

    If you want to lock the record the whole time it's on screen you need to start a transaction before reading it and then close the transaction when the user stops viewing it. When you're in an explicit transaction, locks are retained for the duration of the transaction. However, think VERY carefully before you do this. You will start getting lots of blocking in your application and you may find that undesirable. You also need to find a way of making absolutely sure that the transaction is ALWAYS closed in the event of e.g. a crash or unexpected shutdown. If the transaction isn't closed the lock will be left there indefinitely until a DBA intervenes and manually removes it - so that's more blocking problems.

    A better, but more complex solution, is to do all your database activity via a service that implements the observer pattern. Each instance of the application can then act as an observer of the record it's viewing. If something updates the record, the change is pushed to any instances of the application that are observing it. So you get behaviour a bit like in Windows Explorer, where any new files added to a folder are immediately visible to anyone who's got that folder open.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,404

    Re: I want a concurrency violation and am not getting one

    I used a modified_DateTime column for this (modifiedDateTime DATETIME (or datetimeoffset) NULL). When you select the row always include the modfiedDateTime column. Now when you go to do the update use that as part of the where clause...

    UPDATE tableName SET
    ColumnName = value
    ,.....
    WHERE tablename.PKColumnfortable = pulledPK
    AND modifiedDateTime = pulledmodifiedDateTime;

    If the modified was changed from the orginal the update will fail and you can ask the user to refresh their data and try the update again
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,950

    Re: I want a concurrency violation and am not getting one

    Further to what FD posted, ADO.NET uses optimistic concurrency by default. That means that no data is locked and you can read and write at will but, if you read some data, make changes and then try to save those changes after someone else saves their changes, a concurrency violation occurs and an exception is thrown. It's up to the application developer to catch that exception and decide what to do. Ideally you would retrieve the data again in its current state, merge that into your user's changes for them to inspect before they save again.

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,562

    Re: I want a concurrency violation and am not getting one

    ADO.NET uses optimistic concurrency by default
    Huh! I don't think that's quite right but I'm questioning myself now.

    My belief is that it uses pessimistic concurrency in that it locks the record for as long as it's directly interacting with it at the database level. The fact that an application might then show the record for an indefinite period of time is a separate consideration. Optimistic concurrency is achieved through timestamps in the way that Gary described. But...

    if you read some data, make changes and then try to save those changes after someone else saves their changes, a concurrency violation occurs and an exception is thrown
    That would be optimistic concurrency but I haven't seen that behaviour. I took a quick google and found this which gives some techniques you can use to achieve optimistic concurrency using ADO but I don't think they're the default.

    Is there more to the ADO layer that I'm not aware of?
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,950

    Re: I want a concurrency violation and am not getting one

    @FD, I just tested this code:
    vb.net Code:
    1. Imports System.Data.SqlClient
    2.  
    3. Public Class Form1
    4.  
    5.     Private table As DataTable
    6.     Private adapter As SqlDataAdapter
    7.     Private commands As SqlCommandBuilder
    8.  
    9.     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    10.         table = New DataTable
    11.  
    12.         adapter = New SqlDataAdapter("SELECT * FROM SomeTable", "connection string here")
    13.         commands = New SqlCommandBuilder(adapter)
    14.  
    15.         adapter.Fill(table)
    16.         BindingSource1.DataSource = table
    17.         DataGridView1.DataSource = BindingSource1
    18.     End Sub
    19.  
    20.     Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    21.         Validate()
    22.         BindingSource1.EndEdit()
    23.  
    24.         Try
    25.             adapter.Update(table)
    26.         Catch ex As Exception
    27.             MessageBox.Show(ex.ToString())
    28.         End Try
    29.     End Sub
    30.  
    31. End Class
    I ran the application to load the data. I then edited the data in both the DataGridView in the app and in SSMS. When I clicked the button to save the changes, this was the result:
    System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

    at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)

    at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)

    at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)

    at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)

    at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

    at WindowsApp1.Form1.Button1_Click(Object sender, EventArgs e) in C:\Users\johnm\AppData\Local\Temporary Projects\WindowsApp1\Form1.vb:line 27
    The Data Source Wizard uses the same SQL generation process as the command builder classes so a typed DataSet will be have the same way. Those tools do much as is suggested in the link you provided, i.e. use the original data values as well as the PK in the WHERE clause in order to match records. If you examine the UpdateCommand and DeleteCommand and their parameters, you can see that there is an extra parameter per column with the value drawn from the original version of the data in a DataRow.

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,562

    Re: I want a concurrency violation and am not getting one

    Well I never. So if I'm following correctly, the behaviour is implemented as a result of the update statement that's auto generated for the binding source, is that correct? That's really handy to know.

    It's been a while since I used ADO (I've been working directly in the database for the last few years) so that leaves me two questions:-
    1. Was this always there or was it introduced at some point?
    2. Is it configurable? Ie can I turn optimistic concurrency on and off when having those commands generated?



    @MMock, sorry if I'm, doing my usual job of derailing your thread here. I think the discussion's still pertinent but let me know if not and I'll break it into a separate thread.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,950

    Re: I want a concurrency violation and am not getting one

    Quote Originally Posted by FunkyDexter View Post
    So if I'm following correctly, the behaviour is implemented as a result of the update statement that's auto generated for the binding source, is that correct?
    Nothing to do with a BindingSource. It's the DbCommandBuilder that generates the SQL for the InsertCommand, UpdateCommand and DeleteCommand of a DbDataAdapter so that's what does it. The same principle (same code?) is used by the Data Source wizard to generate those commands for table adapters in a typed DataSet.
    Quote Originally Posted by FunkyDexter View Post
    1. Was this always there or was it introduced at some point?
    I'm fairly certain that it's always been that way.
    Quote Originally Posted by FunkyDexter View Post
    2. Is it configurable? Ie can I turn optimistic concurrency on and off when having those commands generated?
    Not with a command builder. I have a sneaking suspicion that there is a check box somewhere in the Data Source wizard that controls it for a typed DataSet but I'm not 100% sure as I haven't used one for a good long while.

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,562

    Re: I want a concurrency violation and am not getting one

    I think that's probably why I never observed it. I don't think I ever used the CommandBuilder, I always just wrote my own.

    Thanks for the info.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width