Helen,

> 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.

You are right, but if my UDATE SQL handles 2000 records there will allways
be someone editing one of those rows.
So having 200 users will serious block database performance.

> 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.

No,
msaccess defaults to optimistic locking.
msacces does have transaction control
msacces will compare "the old record in memory" to "the actual record in the
database". If they are the same you can update your changes, if they are not
the samen msaccess will warn you that "in the mean time" someone else
changed that same record.

> 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.

I don't mean "dirty read". I just mean a dynamically generated UPDATE
statement in TIBO_database only having the actually changed fields of that
record. (see my answer to Svein)

So perhaps I was not very clear.

Leon


"Helen Borrie" <helebor@...> wrote in message
news:5.1.0.14.2.20030721171929.03c88d60@......

> 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
>
>
>
>
>

___________________________________________________________________________

> IB Objects - direct, complete, custom connectivity to Firebird or

InterBase

> without the need for BDE, ODBC or any other layer.
>

___________________________________________________________________________

> http://www.ibobjects.com - your IBO community resource for Tech Info

papers,

> keyword-searchable FAQ, community code contributions and more !
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>