A little design help concerning locks

[Clayton Arends]

Dear Board (most likely MKALTER

),

I am looking for a little help concerning design of the following scenario. I am trying to make my application make use of the database in a manner in which multiple users (from different machines) can edit in the system at the same time and not step on each others toes. If a user is currently editing a record it is locked while the user is editing and unlocked when the user saves or cancels changes. If another user attempts to edit the same record an error message displays telling the user to "wait in line." All of this currently works with the following caviots.

The problem I have is that if I have multiple locks for different records (on the same machine) and a change to one of the records is committed then the locks for the other records are removed (because that's what happens when Commit() or Rollback() is invoked for the session). What I am currently doing is keeping track of all records that should be locked and after a commit or rollback I iterate through the list and relock the remaining records.

The problem I have with what I'm doing is two-fold. 1) There is a moment (however tiny) between the Commit and the relocking where another user might be able to gain a lock on the record and begin editing before the lock is reissued to the user who is really the one editting the record. 2) I have to add the call to relock the records everywhere in my code that can possibly commit or rollback the session. This is quite redundant and messy.

So my question is this. How can I make this system of locking better, more efficient?

1) Should I make my own Commit and Rollback methods which will invoke the real Commit or Rollback and then relock the records? This would be okay but obviously isn't very good since I would have to make sure all of my code invokes the correct method.

2) Is there an event that will fire after any commit or rollback? This would be better since I can even perform a commit or rollback inside a query and not have to worry about missing the call.

3) Is there a better method of locking records of a table that I'm missing? I'm basically an intermediate Oracle developer (and then only slightly knowledgable of all that is available to me). For instance, I just found out today that I can lock a record in a table.

Here is the method I use for locking a record. Whenever I want to lock a record of a table I perform a query somewhat like the following:

SELECT * FROM table WHERE conditions FOR UPDATE NOWAIT;

If there is a better method then please instruct me. The solution has to be able to work in Oracle 7, 8, 8i +, and it has to work with the basic packages that are installed on every Oracle installation since I cannot rely on all users having all of the cool packages installed.

Also, this is a medium sized system that stores many different sorts of data. It isn't rare that a user will be editing five records at the same time. So limiting a user to editing one record at a time is not an options.

Thanks for any help,

Clayton

 

[Clayton Arends]

I read a few more entries in the on-line help and realized that I forgot to mention an important detail. I do not use TOracleDataSet. I use TOracleQuery exclusively.

 

[jpickup]

The way I see it you have two options:
1. Each record that needs to be locked (independantly of all others) must be locked in its own session. The transactions are therefore independent.
2. Don't use database locks, write you own locking mechanism, for example one where you 'check-out' and 'check-in' records.

The first of these is expensive in terms of database connections. Also, long transactions can cause problems so I tend to avoid them.

 

[Clayton Arends]

Thanks for the reply.

I had thought about #1 as well and came to the same conclusion. But, this is the only method (that I can see) where all other records remain locked after a commit.

As for #2, do you have a suggestion for how to support the inevitable dropped connection situation? I previously used a situation like this (a lock table) and whenever a connection is lost, network problems, etc. then the lock remains. I then had to give the option of overiding and removing the lock to anybody that wanted to open the record. This is dangerous as well.

Perhaps there could be a timeout situation where the record must be relocked every X seconds or the lock expires, but I find that just as messy and dangerous.

Cheers,

Clayton

 

[jpickup]

I actually believe that allowing users or perhaps a 'power user' to override locks is a good thing.

Of course this all depends on the number of users an application is going to have and their level of sophistication.

Currently I have less than 10 users who sit in close proximity and are therefore able to interact verbally ("are you finished with X yet? I need to update it.")

 

[Clayton Arends]

That design might be okay for orderly situations where everyone makes sure they don't step on each others' toes. My company's applications are used by universities and goverment institutions that can have many different buildings that span several blocks. I have also found that this sort of design will inevitably run into a couple problems.

1) If anyone can unlock a record there will always be some yahoo that won't tell anyone that he's unlocked it. And then data integrity is lost.

2) If only a power user can unlock the record then that person will be on vacation the day something happens and a lock remains in effect. Then, all the peons twiddle their thumbs until someone can find the DBA password or the power-user returns.

Of course, these would hopefully be rare occurrances but when in doubt, Murphy's Law prevails.

However, I think I may have found a suitable solution. I have gone ahead and coded an interface class to TOracleSession that my program will use rather than using a TOracleSession directly. There are several duplicated methods most nameably Commit() and Rollback(). This class also keeps track of a list of locked records. Relocking must still take place whenever the session is committed or rolled-back but this scenario hopefully reduces the amount of time that it takes to relock.

When my interface's Commit() method is invoked a TOracleQuery object is created. I populate it with a PL/SQL block:

declare
temp int;
begin
commit;
-- select statements
end;

Following the commit are all of the select statements that will relock each record. Just for an example, here is how one might look:

select table_id into test from table where table_id = 3 for update nowait;

Using this method, the chances that someone can get a lock on a record between the commit and before the records are relocked become astronomically low. I originally thought about using a TOracleScript so that each column lock didn't have to adhere to the same format (one select variable that must be an int and *must* use the INTO clause). But, AFAIK a TOracleScript executes each query separatly rather than sending the entire contents to the server at the same time.

Now, this solution seems to be the best for resources (I don't have multiple sessions open) and works within the Oracle locking mechanism so that there aren't problems if a connection is dropped. The next step would be to create a lock table that stores the name of the table, record id, and user that has locked the table simply to be able to see who was the last person to lock the row. It wouldn't control the locking process but would be referred to if a record was found to be locked.

If anyone can see how this scenario can be improved upon or can see possible problems then your comments would definitely be welcome.

Cheers,

- Clayton

[This message has been edited by Confusu (edited 10 August 2001).]