Subject Re: Record Locking (was: IBO - Long time opened Query)
Author Christian G├╝tter
Hi Lester,

thank you very much for your comprehensive explanation.

> It does need some housekeeping though, as you can lock
> records and leave them locked.

I see this kind of housekeeping as a little downside of your
method. The state that a record can still be marked as
'opened by user X' after eg. the computer of user X has
crashed is something I would like to prevent. That was why
I was suggesting to set the locking field and let the user
edit the document within one transaction. This would mean
that the setting of the locking field would be rolled back
when the user's computer crashes.

Of course, my way of doing it has the downside that a
transaction is left open for a longer time and that the
user will lose his work when the transaction is rolled

> I have a sort of 'clear all'
> which resets any outstanding 'X' flags when X logs off and
> also when they log on again, in case of a network crash, but
> I have not needed the 'supervisor override' to unlock a
> problem record.

There are two reasons why I would not be very happy with this

1. It means a lot of work for the server to scan and update
all tables containing documents when the user logs in or
logs off.
2. You have to make sure that this 'clear all' is not
executed when a single user has more then one connection
to the DB. (Which is not quite difficult, I agree.)

From a technical point of view, I like my approach with only
one transaction better, but thinking of my users (some of them
would definitly keep documents open for half a day), I tend
to prefer your approach, because we all know how OAT issues
can srew up the server.

Firebird 1.5 will offer the 'SELECT FOR UPDATE WITH LOCK',
but in our case, it wouldn't change a thing (IMHO), because
locking records in this way would AFAIK still mean that
a transaction could be open for a very long time.

Up to now, I don't see an ultimate way to lock records that
might be edited for a long time. If someone knows this way
<g>, it would be great if this person would post it in this
group. Anyway, it would still be great to discuss some other
approaches. As I said, it is the first time I am thinking
about storing documents in the database and so there might
be a lot of issues I did not see or did not even think