Database Concurrency

[wakh]

Hi Guys,

Here I would like to get opinions of fellow programmers on a very important database topic, known as concurrency. We all know that in enterprise grade applications multiple users use a single value from a single table at the same time, or even worse might make decisions based on a single value and update the contents of the database based on that value. Now if the value has not been changed until the update occurs then everything goes fine, but incase the value on which a crucial decision has been made is changed in between all this procedure by some other transaction then it introduces a great threat because the decision has been made on a value which is no longer valid. So my question is how to tackle such a scenario?

Database management systems by various vendors employ different techniques to handle such a scenario at the database level, but the question here is how to deal with this problem in our applications?

I hope that I have been clear in describing the problem. Hope to have a great constructive discussion on this topic.


Regards,
wakh

 

[jimmajsterski]

If I recall correctly, the application which could modify a db record is given write or modify permission on the record, and no other app which depends on that record is allowed access unitl the writer/modifier has exited. There are a few db gurus on this forum who know exactly how this is done, and they will guide you.

 

[wakh]

jimmajsterski: Thanks for your input. That's how the database management engine deals with such conflicts internally, and this mechanism is known as 'Locking'. But the question here is how to take care of such cases in our application, or how to acquire those locks through our applications in order to avoid such conflicts?

 

[jimmajsterski]

wakh- if you are using ado.net, research OleDbTransaction object or SqlTransaction object in System.Data.OleDb or System.Data.SqlClient namespaces respectively.

 

[wakh]

I am aware of SQLTransaction, but it does not handle concurrency problems.

 

[cjard]

Go for optimistic; it's available as an option in the dataset wizard anyway. An update will attempt to update a record only if all the values of the row are equal to the values as they were when the row was retrieved.

i.e. you call

tableAdapter.Update(row)

which calls:
UPDATE x SET a = @new_a WHERE id = @id and a = @original_a

THe parameters contain new and original a values, and if the command affects 0 rows, a DBConcurrencyException is thrown if 0 rows were updated

The only time, of course, that such a command will throw such an exception is if another user already affected the data.


Also, use Oracle. Its concurrency handling is deemed better than SQLServer's

 

[wakh]

Yes that is an option but this can be applied if the rows are being retrieved into the application (for example: Datagridview) but what could be a solution to the problem which occurs when for example: a user tries to create an invoice with a particular quantity of a particular item which has the stockInHand value of 5 (the invoice includes other items also), but while the user is creating the invoice another user executed the transaction against the same item which rendered stockInHand value of that item to 0. Now when the first user tries to execute the transaction, it will result in an error?

How such a scenario can be tackled?

 

[cjard]

How such a scenario can be tackled?

It's not really something I can answer

i.e. would you ask me:
"I'm getting the user to type the name of a file and click a button, then I process the file. If the user types a wrong filename, a FileNotFoundException is raised. What should I do?"

or:
"I'm designing a form to do X. What should it look like?"

or even:
"What is my favourite colour?"


Can you see how this is not something I can instruct you on?


What would I do? I'd probably MessageBox "Another user seems to have edited this record during the time you had it open. Here are their changes: <CHANGES>
Do you want to Overwrite them with yours, Merge them with yours or Discard your efforts?"

 

[wakh]

You are missing the point here, for detecting the changes you need to know the original values which are not known in this case? But anyways I have found the answer.

 

[cjard]

Why do you not know the original values? A DataRow keeps track of them for you, and a TableAdapter parameter collection is configurable to pull either the current or the original version of the value. By default, when "Use Optimistic Concurrency" is ticked, the generated code (below) utilises this feature (note that the code below is not optimistically concurrent, it just demonstrates the use of the original and current row versions).

Me._commandCollection(1).Connection = Me.Connection
Me._commandCollection(1).CommandText = "UPDATE TABLE_X SET FILE_REF = [I]:FILE_REF[/I] WHERE (BANKEE_REF = [B]:Original_BANKEE_REF[/B])"
Me._commandCollection(1).CommandType = CommandType.Text
Me._commandCollection(1).Parameters.Add(New OracleParameter("[I]FILE_REF[/I]", OracleType.Number, 8, ParameterDirection.Input, "FILE_REF", [I]DataRowVersion.Current[/I], false, Nothing))
Me._commandCollection(1).Parameters.Add(New OracleParameter("[B]Original_BANKEE_REF[/B]", OracleType.VarChar, 17, ParameterDirection.Input, "BANKEE_REF", [B]DataRowVersion.Original[/B], false, Nothing))

If youre not using a datarow, and you're e.g. storing the values you downloaded into an array and then overwriting them with the new ones, then of course you don't know the original values (because you overwrote them), but the solution to that is simple..

 

[wakh]

Well, the data is not being extracted from the database at all. Its just a scenario where you don't want to allow another user to make changes to specific records because they are being used by you. Its a pessimistic concurrency record locking scenario?

 

[cjard]

Now youre confusing me. This statement:

UPDATE table SET col = :new_col WHERE id = ld_id and col = ld_col

Is optimistic. It HOPES that no other user has changed "col" since I downloaded it. I only execute an update to "col" when its value is the value most recently known to me. If this update affects 0 records, then someone else changed the row before I did.

This is optimism.

Compare with pessimism:
Pessimistic Locking

 

[wakh]

Yes, I do know what is optimistic and pessimistic locking. The question here is how one can lock records through VB, so that no one else is able to modify it untill the lock has been released?

I'll explain the scenario with an example now to make the picture more clear. Consider Inventory Management System which got the invoicing capability. Now suppose I am in process of creating an invoice which got many line items in it, of those one is ItemA. The StockAtHand value for ItemA is 50 which I find out by checking the Inventory List. The customer wants 48 ItemA, so I put 48 in the Quantity column. After that I continue adding other line items which the customer wishes to purchase. Now imagine, while I am in the process of adding other items, another user also starts creating an invoice with only one item in it i.e ItemA with Quantity of 15 and executes the transaction before me, rendering the StockAtHand value of ItemA to 35. Now when I try to execute the transaction after I finish adding all the items, I will get a problem because the StockAtHand value of ItemA is now less than 48 which is mentioned in my invoice.

That's the scenario I need to tackle, because in a multi user environment it is highly likely that such conflicts will occur, especially if there are very fast running items. The only workaround I have found is to lock the records, if you know or can think of any other way to tackle this problem then do let me know, as I am having this project coming my way for development in sometime.


Edit: As usual the database management system that will be used for this project is SQL Server.

 

[MattP]

Yes, I do know what is optimistic and pessimistic locking. The question here is how one can lock records through VB, so that no one else is able to modify it untill the lock has been released?

1 way:

Make a bit column in your database.
Set it to 1 when a user opens a record.
Set it to 0 when they're done with the record.
Don't let people access rows that have a 1 in that bit column.
Pray your app doesn't crash or the person leaves work with a record locked.

 

[wakh]

Yes I thought of that way, but mate it is very risky to use it. Can't take chances like this especially in application which will be used to manage such an important asset of the business.