At 09:07 AM 21/07/2003 +0200, you wrote:

>Hi,
>
>If user A changes an important field in a record (invoice state for example)
>while user B is editing that same record, alle changes made by user A will
>be overruled when user B updates that record.
>This could cause serious problems, because user A must be sure of the
>results of the update query.

Not if user A asks for a pessimistic lock on that row. Once A has the
lock, other users can only read the row. If user B tries to edit it, he
won't be able to.

>There are several solutions:
>-msaccess will not allow to update a record "changed by another user". That
>works great

It works great when you have about 3 users on the system. Beyond that, it
stinks. MSAccess always uses pessimistic locks, whether needed or not,
because it has no transaction control and no row-level updating.

>-dbexpress will only put "changed" fields in the update statement, thus
>merging the two changes.

Not for InterBase it won't. IB (and Firebird) don't support "dirty read"
so either a whole row gets updated or nothing gets updated.

>But what is the correct solution for this problem using IBobjects? Is there
>a way to only put "changed" records into the update statement.

I have no idea what this question means. A dataset update is posted for
only the current row. If the update fails because another transaction has
a lock on the row (i.e. that other transaction has already requested
(posted) an update then nothing further can happen except that your
application handles the conflict according to what it needs to achieve in
this situation - usually to cancel the update.

Multiple row updates can be done by passing a searched update
statement. If one row in the searched set can't be updated because of a
lock, then no rows will be updated.

>If no, what to do ?
>
>-you could allways use pessimistic lockings, but it will block the system to
>much with more than 30 users

"Pessimistic locking" is a client-side hack. If you set Pess.Locking true
on a dataset then what happens is this: as soon as the user calls Edit on
that row, IBO sends the server a dummy update statement request for the
underlying row. If the dummy update request succeeds, then that user
(transaction) has GOT the lock on that row. It doesn't affect any other
rows. It means no other transaction can successfully request (post) an
update to that row until the transaction completes (commits or rolls back).

>-you could make the most important fields "read only" preventing IBO to use
>them in an update statement, but that's not 100% safe.

I don't get this, either. Either you want the rows to be updatable or you
don't. If a pessimistic lock request succeeds, that row will automatically
become read-only for all other transactions. Conversely, if this
transaction asks for a pessimistic lock, and some other transaction already
has that row locked, then this user will discover that the row stays in
Browse mode - it won't even go into Edit mode.

Just remember that IB/Fb are not Access - they are nothing like
Access. Transactions isolate one user's work from another's (or, for a
single user, they isolate one of her tasks from others). Think "optimistic
row-level locking" - any successful Post locks the row. It doesn't need
pess.locking to make this occur - it's just the way it is meant to be.

Pess.locking really isn't necessary most of the time (which is why the
database doesn't implement it). It just is useful in some conditions, to
*guarantee* that the first user to move a record into Edit mode (a
client-side state that the database doesn't know about) will post before
she actually begins the real editing.

Helen