Hi all. I have a question about database locks. my application can be used concurrently by many users at the same time. users may read, update and insert data at the database. my question is : what is the lock should be used in this situation ? read lock ? write locks ? exclusice write locks ? thanks....
Joined: Aug 07, 2003
Your application requirements will drive this, but as you've described it, no locks are necessary since users are allowed to all those things concurrently.
Now, if you had a requirement such as "no user may insert a row while another user is viewing data" for example, while you could use a table lock while the second user is viewing data, things will get complicated very quickly. What happens if that second user goes to lunch or leaves for the weekend while the window is still open, holding the lock? Must everyone else stop their work? Besides, this type of requirement is rarely needed in reality. Locking usually sounds good at first but becomes obviously flawed when investigated more deeply.
The only requierment you typically have is "two users may not modify the same row simuntaneously as data corruption can occur," and most databases give you this for free by way of transactions. Consider exactly what your needs are and repost with details. Then we can assess if locking of some sort (database or application) is needed.
Thanks alot. my two applications are : 1. a web forum (like JavaRanch).. 2. an online reservation system (just like the example from EJB 4th edition).. any ideas about the required locks for these apps ?? thanks again..
Joined: Aug 07, 2003
Neither of these cases should require additional locking beyond that provided by performing the updates and inserts in a transaction. Can you be more specific? What application-level locking do you need? Here are some examples:
Each thread has a field holding the user ID of the last user to post to the thread. When someone posts, you insert the post and update the thread's user ID. Normal transaction properties will handle this.
When a user clicks "reply," the thread is locked against any other user clicking 'reply" until the first user either cancels or submits their reply. Not only is this a bad idea for the reasons I mentioned above, but it would span two transactions and thus require your own locking table -- e.g. thread_lock ( thread_id , user_id , lock_ts ).
A user searching for a flight is presented with a list from which to choose. When they choose a flight, it books a temporary reservation that holds their place for up to 5 minutes while they varify the dates, times, and airposts and arrange payment. If they cancel or time out, the reservation is freed. If they accept and their charge goes through, the reservation is finalized. Again, regular transaction properties take care of this, but you need to have a status field on the reservation marking it as "on hold" until finalized. I would consider this basic application logic, not so much "locking."
As you can see, I can make up a bunch of random requirements that require different forms of locking. If you can clearly state your application's requirements, I or someone else can provide a straight answer.