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

 

[cjard]

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.

I see this scenario on Maplin's website. They quote a stock level and you can add things to your basket. Some items are guaranteed to remain in your basket for 45 minutes after you added them. After that, they are released..

As such, you'd have a basket area and a stock area, and items are literally put from stock to basket by decrementing one table and incrementing another. Either on a timer (probably what I'd do; oracle has job scheduling for stuff like that) or on an event raised by session expiry, move items back into stock if they have been in a basket longer than X.. Or maybe better, if the basket loader has been inactive longer than X (indicating they have wandered off/left the computer)

THere are many analyses that can be done in this scenario, including how long a basket loader takes to complete and how reliably they complete. For people who actions define them to be dawdlers, extend the timer.. For people whose actions define them to be insincere messers, shorten the timer

 

[wakh]

Well that's one solution to tackle the problem. By the way the application will be a standard desktop application, not web application. I guess I would have to use timers on forms to increment/decrement the appropriate tables? But there's a possibility of application crashing, in which case the items moved by the user before the crash would stay in the temporary tables?

 

[cjard]

Why get the client machines to do it? I'm talking about a server originated "session" type arrangement. Web browsers do not send a 10 minute timeout to the bank web site instructing the site to time out their login, do they?

 

[wakh]

OK, how this or possibly "sessions" can be managed on server side? Yes web browsers doesn't send timeout notice, but this won't be a web application?

 

[cjard]

A browser is a display and control component like a windows form. The difference in this context is academic. You can implement a windows form app that is session based, it's just that web apps already are because HTTP connections do not persist so some way of saving state had to be envisaged.

I'm starting to get lost as to what youre driving at, but if you wanted to build a windows forms app that had a shopping basket with a 10 minute inactivity timer you could:

Have it in a database
Have the winforms app intereact with the db using stored procedures
Every stored procedure updates a table with a datetime of the last activity you had
A timed job on the server runs every tminute and empties baskets where last user activity was more than 10 minutes ago
Users would be told "if you spend longer than 10 minutes, the db will clear your basket" when they go for coffee break etc


Just because IIS and a web app needs a session based system doesnt mean that other kinds of app cant have it, or indeed that if you need a session based system you have to use IIS and a webapp

Think a little more.. Unconventionally. Ultimately you have to sit in your chair and think "IIS has sessions. It manages its sessions in some way. What is that way? Can I make a database server manage sessions in the same way?How?"

 

[wakh]

Well, that's a possible solution to the problem, wasn't clear what you meant in earlier post. Yes, job scheduling can be used to carry out this procedure on the server side. A plan needs working out for its implementation now. The only drawback I see with this approach is that items will be unavailable for sometime.

P.S : I am having hard time in reaching the forum due to the recent undersea cables breakdown.

 

[cjard]

The only drawback I see with this approach is that items will be unavailable for sometime.

The only thing you can do if this is acomplaint, is allow the basket to be filled and only fulfil the order when the payment is mader, thus allowing people to basket items that do not exist because in between being basketed and being paid for, somone else bought them

All options are possible but some are mutually exclusive. You either block the resources while they are ijn the basket or run the risk of them not being there come checkout time. Optimistic vs pessimistic locking

 

[wakh]

That's true, will have to take one approach, either have to block resources or keep them unblocked in which case risk is there...!!