Can I use another db file for optimistic locking?

[Anton Kuzmin]

What do you think? Is it allowed? I want to keep necessary data separate because it's not allowed to change structure of original db file in my project. I could use "owner" column for optimistic locking at room booking but it won't help in situations when two different people update the record.

[Anayonkar Shivalkar]

Anton Kuzmin wrote:Is it allowed?

It can be checked by if it is breaking any must requirement from your requirement document.

Anton Kuzmin wrote:I want to keep necessary data separate because it's not allowed to change structure of original db file in my project.

I am curious about this. What is this necessary data? And why will it alter the structure of original db file?

Anton Kuzmin wrote:I could use "owner" column for optimistic locking at room booking but it won't help in situations when two different people update the record.

I don't get what do you mean by this. Only one thread will get a lock on a record at a time, and if the room is booked by one thread, all other threads will get error (i.e. exception at code level) that room is already booked etc. It's as simple as that.

[Anton Kuzmin]

Anayonkar Shivalkar wrote:

Anton Kuzmin wrote:I could use "owner" column for optimistic locking at room booking but it won't help in situations when two different people update the record.

I don't get what do you mean by this. Only one thread will get a lock on a record at a time, and if the room is booked by one thread, all other threads will get error (i.e. exception at code level) that room is already booked etc. It's as simple as that.

But in my program, people will also be able to update data like price. In this case "owner" column is not enough.

Anayonkar Shivalkar wrote:

Anton Kuzmin wrote:I want to keep necessary data separate because it's not allowed to change structure of original db file in my project.

I am curious about this. What is this necessary data? And why will it alter the structure of original db file?

I am not sure whether I am violating the rules, so I'll be short.
This necessary data is "version" column. It will make optimistic locking work.

[Roel De Nijs]

So you want to add extra complexity (which is not required at all) and these extras give you a lot of issues, then my 1st question would be: why do you add these extra functionalities which are not required. I always wonder why people just can't implement only what's required.

As an answer to your question: you definitely can create a 2nd file although I would advice against it. Because I have following concerns:

it adds extra complexity without any benefits (because not required functionality), so it violates the KISS-principle

what will your application do if this highly important file could not be created (e.g. because you are in a read-only directory)

how will you tie the records in the original file with the optimistic locking file? because you don't have a primary key, so you can't uniquely identify them. Or will you only update this 2nd file, because it's a copy of the original db file with an extra column? That's definitely violating requirements (because original database file is still used by other applications). Or maybe you update both files (which gives some other "transactional" issues: what if 1st file is successfully updated, but 2nd one fails)

Hope it helps!
Kind regards,
Roel

[Anayonkar Shivalkar]

Anton Kuzmin wrote:But in my program, people will also be able to update data like price. In this case "owner" column is not enough.

Is it what your requirement doc says? That operator should be able to update all (or more than one) columns of a record? Just make sure if this is really necessary.

Besides, I would suggest to acquire lock on the row so that the thread could update anything there.

Yes, it is not optimized. Yes, if one row is locked just to update one column, no other thread could update any other column of that record. And yes, it will degrade the performance.

But data integrity is of extreme importance than performance (at least from OCMJD's perspective).

I hope this helps.

[Anton Kuzmin]

Anayonkar Shivalkar, no, instructions don't say that but DbAccess interface has this method:
// Modifies the fields of a record. The new value for field n
// appears in data[n]. Throws SecurityException
// if the record is locked with a cookie other than lockCookie.
public void updateRecord(long recNo, String[] data, long lockCookie)
throws RecordNotFoundException, SecurityException;

I know that people on this forum say it's OK to implement the interface and not use it anywhere in the application. But I am not convinced that it's what Oracle/Sun wanted.

Of course, I use locks to update the record.

[Anayonkar Shivalkar]

Anton Kuzmin wrote:public void updateRecord(long recNo, String[] data, long lockCookie)

Well, anyway your are sending String[] to update the record (which I assume is a complete record). So, why bother about which column is being updated? Just acquire lock on complete record, and send an array of String as new record.

I hope this helps.

[Anton Kuzmin]

The thing is that two operators can open up the same room record in their UI.
One changes price, clicks 'Save', records gets locked, updated, unlocked.
Then other guy decides that he wants to update 'Date available', clicks 'Save', record gets locked, updated, unlocked.
Result: Record has old price.
With optimistic locking enabled along side with current locking rules, second guy will get nice message that data has been changed while he was thinking and he needs to reload it.

I do not do logical locking at record opened for edit because it creates even more problems like whether to use lock timeout, waiting for lock for very long time.

[Anayonkar Shivalkar]

Anton Kuzmin wrote:Result: Record has old price.

Again, updating price - is this must requirement? As far as I know, we have to update only one column as must requirement (at least till few months ago - I don't know if now it's changed or what).

Anton Kuzmin wrote:With optimistic locking enabled along side with current locking rules, second guy will get nice message that data has been changed while he was thinking and he needs to reload it.

Well, this also, does not look like a must requirement. As Roel has already mentioned, this will simply complicate the solution (and you don't get any extra points for doing things which are not asked).

Anton Kuzmin wrote:I do not do logical locking at record opened for edit because it creates even more problems like whether to use lock timeout, waiting for lock for very long time.

Why to use lock timeout? As you've already mentioned, during updation, lock the record, update it, write it, and unlock it. To make your life easy, you can think of thin client approach.

I hope this helps.

[Roel De Nijs]

I don't know if I should post again, because it appears that my previous response was simply ignored (by the OP).

I'll try once again: I fully implemented the update-method (just like delete and create methodes), so through this method you can update "price", "date available",... But in my GUI you can only book a room (so no other modifications are possible through the GUI), so there is simply no need for an optimistic locking mechanism (or an alternative). In a future release of the application they might decide to add full updating capabalities and then this will definitely be an issue which should be handled appropriately. But for now, it's not needed and it might never be needed (maybe updates are done by some batch job at night) and will only increase complexity (with even more bugs, less cleaner code, loss of points,...), so I would call YAGNI on this one.

[Anton Kuzmin]

Thank you! I think you broke most of my doubts