Home » SQL & PL/SQL » SQL & PL/SQL » simultaneous multi-user application (Oracle, 9.2.0.1.0, Windows XP)
simultaneous multi-user application [message #428286] Tue, 27 October 2009 14:14 Go to next message
Moksha
Messages: 16
Registered: October 2009
Junior Member
Hi!
I need to create an application in which multiple users would be using this application simultaenously. Front-end is in ASP.net and back-end is Oracle 9.2.0.1.0. Can you please let me know how to make this application for simultaneous multiple users access from Database prospective, like row or table locks etc., or any other. For example, if there are some 10 tables get updated with the values entered in the front-end, then how I can make the database get updated simultaneously? If you can explain with a small example then it would be great or please point me to any existing information.

Hope, I am clear in my query.

Thanks,

Regards,
Moksha

Re: simultaneous multi-user application [message #428287 is a reply to message #428286] Tue, 27 October 2009 14:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have nothing to do Oracle is built to handle simultaneous sessions.
Nevertheless you have to know the basic on databases and concurrency, I advice you to read at least the first chapter of Database Concepts

Regards
Michel
Re: simultaneous multi-user application [message #428343 is a reply to message #428286] Wed, 28 October 2009 00:34 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
to continue with Michel's thought,

Since your app is written in asp.net, I am assuming your front end will be STATELESS (runs via a web browser for example). If so, then Oracle's locking is only part of the answer. You will also have to consider what is called "OPTIMISTIC LOCKING". The concept is simple enough, but like most things:


the devil is in the details...
its easier said than done...
if it was that easy everyone would be doing it...
it compiled, must work...
he who travels light is a happy man (Quan Hong Lin)...
<your favorite reality phrase goes here>


You will have to do some reserach online to get the detail but here is the basic idea.

When an application reads a row it must consider who else might read/write the same row. In doing this the app has basically two choices:



1) the app can take a pessimistic view and assume that someone else will change the row while the app is looking at it and using it.

2) the app can take an optimistic view and assume that the row will remain unchanged while the app is looking at it and using it.



Its one or the other and the choice your app makes deterimines what locking style it uses. Consider for example that traditional locking is only really necessary when two people want to use the same row. If you assume that a second person will not use a row while you are looking at it then there is no need for any row locking right? Thus we can not lock any rows in the traditional sense and just check the row when we do the update to see if it has changed. The check becomes our locking strategy.

Pessimistic: Stateful apps (client server for example) maintain a constant connection to the database. In doing so they can rely on the database native locking mechanisms to protect themselves from changes made by other users while they are working with rows. If they want a row to remain unchanged while they use it, they lock it. The row remains theirs until they commit or rollback. They can do this because they maintain a continuous connection to the database. This is called "maintaining state", or "a stateful app".

Optimistic: Stateless apps (think internet apps), do not keep a contant connection to the database. They connect, do something, and then disconnect. For example: they connect to the database, read a set of rows, and then disconnect. This is a problem because it means the native locking mechanisms of the databse are not sufficient to protect against the problems of LOST UPDATE and the like (remember your basic schooling about locking strategies and update anomalies by C.J.Date?). So another locking mechanism must be used to eliminate or at least reduce the potential for lost update (and all those other update anomalies). Enter the OPTIMISTIC LOCKING strategy.

The terms pessimistic and optimistic don't really convey much meaning other than to say that locking is more involved if you do it than if you don't. Optimistic locking is not really locking at all. There is no row lock ever taken. By assuming no locking will be required, it is beir example) maintain a constant connection to the database. In doing so they can rely on the database native locking mechanisms to protect themselves from changes made by other users while they are working with rows. If they want a row to remain unchanged while they use it, they lock it. The row remains theirs until they commit or rollback. They can do this because they maintain a continuous connection to the database. This is called "maintaining state", or "a stateful app".

Optimistic: Stateless apps (think internet apps), do not keep a contant connection to the database. They connect, do something, and then disconnect. For example: they connect to the database, read a set of rows, and then disconnect. This is a problem because it means the native locking mechanisms of the databse are not sufficient to protect against the problems of LOST UPDATE and the like (remember your basic schooling about locking strategies and update anomalies by C.J.Date?). So another locking mechanism must be used to eliminate or at least reduce the potential for lost update (and all those other update anomalies). Enter the OPTIMISTIC LOCKING strategy.

The terms pessimistic and optimistic don't really convey much meaning other than to say that locking is more involved if you do it than if you don't. Optimistic locking is not really locking at all. There is no row lock ever taken. By assuming no locking will be required, it is being "optimistic".

So how does it work? There are several means. Here is one way, the oldest but not most elegant way:


1) when your app reads a row, you make a read only copy of it and stick this read only copy in memory somewhere.

2) when you are about to update the row on the database, you re-read the row from the database and then you compare what you just got to the unchanged copy you saved earlier. If they are the same then you know the row is unchanged so you do the update. But if they are different then you know the row has been modified after you first acquired it so you raise an error and do not do the update.

This simple example clearly has some holes but you get the idea I hope. The basic reason that optimistic locking was postulated as necessary goes something like this:

you made your decisions on what to do with the row you read based on the data in the row at the time you read it. But if the row changed since you initially read it, then your changes may not be valid for the data on the row as it is now. Thus you should "start over" with your update by re-reading the row and deciding if you still want to do your update after you have looked at the changed row as it is now. Failure to "start over" means your update may be invalid.

OK, so I talk a lot. The bottom line is, you must do a GOOGLE for "OPTIMISTIC LOCKING" and read up on the various implementation alternatives and then select one. But here is a caution:

Although it is possible to implement Optimistic Locking totaly in the application, it is usually implemented with some help from the database (a column is usually added to each table for this purpose) because it simplifies things a lot. This means that there will need to be some aggreement between the application areas and the database areas that optimistic locking is necessary and that XYZ method is how you will be doing it.

There are other considerations as well. For example:


1) will you allow multiple optimistic strategies to co-exist or will you force all apps to use your one optimistic methodology?

2) will you allow stateful apps to co-exist with your stateless apps on the same database which again implies multiple different locking methodologies co-existing on the same database?

It is easy to say NO to these questions, but there are significant costs. For example, if you require all apps going against y

[Updated on: Thu, 29 October 2009 02:32] by Moderator

Report message to a moderator

Re: simultaneous multi-user application [message #428407 is a reply to message #428343] Wed, 28 October 2009 04:18 Go to previous messageGo to next message
Moksha
Messages: 16
Registered: October 2009
Junior Member
Hi Michel & Kevin,
Thank you very much for your valuable inputs. I have gone through the optimistic locking document (http://www.orafaq.com/papers/locking.pdf)and how to use optimistic locking with concurrency. From the search & reading, I understand that many large applications including B2B applications use optimistic locking with concurrency and the best way to achieve concurrency is to use dbms_utility.get_time function as one of the key value in order to overcome any concurrency failure and use this concurrency technique with triggers instead of carrying in stored procedures. From my reading, I found that it is suggested to use pessimistic locking for stateful connections (like client/server, java servlets etc.,) and pessimistic locking for stateless connections (like HTTP). I have a littel confusion here, HTTP applications are not client server applications? I mean, if browser request with web server is not client/server? If so, then should I use pessimistic or optimistic locking? Please, excuse me if my query looks stupid or silly.

My application is like a browser based data enty application in asp.net fron-end and many users would be using this to enter the data, modify the data, delete the data, take reports for management etc., simultaneously. This application will be hosted on a webhost then only the registered users will have access to this application and work. Say, the users will work from 9:00 AM - 5:30 PM on this application simultaneously.

In such kind of applications, is it OK to go for optimistic locking or pessimistic locking like select... for update? Based, on your experiences, can you please suggest the best on? I want to know your valuable advise from your experience so that I can avoid any unnecessary re-invention and make sure that I am going in the right direction.

Please, comment.

Thanks,

Regards,
Moksha
Re: simultaneous multi-user application [message #428498 is a reply to message #428286] Wed, 28 October 2009 09:21 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Sorry all about the stupid wrapping of text. It formatted fine when I previewed.

If one of the monitors could edit my post and remove the CODE tags that would be nice.

Moksha

1) for a stateless app, use some form of optimistic locking.
2) talk with your DBA and ARCHITECT and see what implementation they wanted.
3) be sure to ask about apps from other areas, what are they doing?
4) ask about the plan for future apps and who will enforce the rule

** remember, optimistic locking is not the same as pessimistic locking. they do not do the same thing.

Pessimistic locking allows a transaction to preclaim locks on rows. This prevents other transaction from using those rows.

Optimistic locking can only fail your transaction. It cannot prevent others from attempting what they want.

To understand this consider the meaning for SELECT ... FOR UPDATE in a stateful app, then a stateless app.

Locking only works if everyone does it. Another app can forget to optimisticly lock which leaves them open to break things.

Good luck, Kevin

[Updated on: Wed, 28 October 2009 09:23]

Report message to a moderator

Re: simultaneous multi-user application [message #428522 is a reply to message #428498] Wed, 28 October 2009 11:11 Go to previous messageGo to next message
Moksha
Messages: 16
Registered: October 2009
Junior Member
Hi Kevin!
Thank you very much. I will explore these suggestions in my application.

Regards,
Moksha
Re: simultaneous multi-user application [message #428610 is a reply to message #428498] Thu, 29 October 2009 02:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Kevin Meade wrote on Wed, 28 October 2009 15:21
Sorry all about the stupid wrapping of text. It formatted fine when I previewed.

If one of the monitors could edit my post and remove the CODE tags that would be nice.

Replaced them with [hr] tags, so it still stands out.
Re: simultaneous multi-user application [message #428706 is a reply to message #428286] Thu, 29 October 2009 10:07 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks Frank, you are the best.
Previous Topic: Getting duplicates from pay_grade_rules_f
Next Topic: what is the valid parameter to utl_file
Goto Forum:
  


Current Time: Sun Dec 11 08:10:28 CST 2016

Total time taken to generate the page: 0.09860 seconds