Hi,

we have one master-table with 10 to 15 detail tables. I want to
activate cachedupdates when the user start "editing" this Master-Table
so i can do a CancelUpdates if he clicks to cancel so all changes in
detail tables are also canceled.

Is this behaviour working with pessimistic locking? I want to do
something like

1. mastertable.edit; >> so it is locked for all other users
try
   2. start cachedupdates
   3. do something
finally
   4. apply or cancel
end;

Or should i use transaction for this?

Any tips?

--
--
Yusuf Zorlu | MicrotronX

Yusuf


My personal approach is to use a transaction.

Roy Lambert

Roy Lambert wrote:

> Yusuf
>
>
> My personal approach is to use a transaction.
>
> Roy Lambert

Hi Roy, thanks for that info. What is when 40 users are editing at the
same time 40 positions in same tables and each starts a transaction. Is
this a problem?

--
--
Yusuf Zorlu | MicrotronX

Yusuf


>Hi Roy, thanks for that info. What is when 40 users are editing at the
>same time 40 positions in same tables and each starts a transaction. Is
>this a problem?

Whichever way you go this "1. mastertable.edit; >> so it is locked for all other users" means there will be a problem. A standard table.edit with pessimistic locking will stop other users editing the selected record. If the detail table's linked records can only be edited when the master is edited then they theoretically do not require any locking at all.

A transaction can only be applied at the table or database level so you'd be locking all users out whilst one made the changes, and cached updates works in an optimistic fashion so will allow multiple users to change data only reflected the problem when applied.

What I normally do is "roll my own" in that I copy data into an in-memory EDBTable or my own nlhStrTable, edit and post back in a transaction. Its sort of like cached updates but different.

Roy

Yusuf,

My first approach to this, a few years ago, was to use Cached Updates, but I have changed my mind since then for many reasons, the main being the need to access the detail rows being edited with SQL. In any case, I always use pessimistic locking - optimistic locking is, most of the cases, completely useless.

The technique I have been using to replace Cached Updates was this:

1. On master table "Edit", create and populate a temporary table with the detail records.
   (I use the lock on the master row as a flag to indicate that the corresponding detail records shall be considered locked)

2. Edit, change, delete, whatever you need to do with the detail rows (in a temp table) and master row.

4. On master table "Cancel", just "Cancel" the master table

5. On master table "Post":
     - Start a transaction
     - delete the old detail rows in the database
     - post the master row
     - insert the updated detail rows in the temporary table
     - commit the transaction

6. Delete the temporary table


--
Fernando Dias
[Team Elevate]

Fernando


That's about it.


Roy Lambert

Roy Lambert wrote:

> A transaction can only be applied at the table or database level so
> you'd be locking all users out whilst one made the changes, and
> cached updates works in an optimistic fashion so will allow multiple
> users to change data only reflected the problem when applied.
>
> What I normally do is "roll my own" in that I copy data into an
> in-memory EDBTable or my own nlhStrTable, edit and post back in a
> transaction. Its sort of like cached updates but different.

Thanks for that, we will also go that way, creating a backup of all
detail-tables in Memory so we can post the data back if user clicks to
cancel.


TIM: What about adding such a functionality in v3?

--
--
Yusuf Zorlu | MicrotronX

Fernando Dias wrote:

> Yusuf,
>
> My first approach to this, a few years ago, was to use Cached
> Updates, but I have changed my mind since then for many reasons, the
> main being the need to access the detail rows being edited with SQL.
> In any case, I always use pessimistic locking - optimistic locking
> is, most of the cases, completely useless.
>
> The technique I have been using to replace Cached Updates was this:
>
> 1. On master table "Edit", create and populate a temporary table with
> the detail records.     (I use the lock on the master row as a flag
> to indicate that the corresponding detail records shall be considered
> locked)
>
> 2. Edit, change, delete, whatever you need to do with the detail rows
> (in a temp table) and master row.
>
> 4. On master table "Cancel", just "Cancel" the master table
>
> 5. On master table "Post":
>      - Start a transaction
>      - delete the old detail rows in the database
>      - post the master row
>      - insert the updated detail rows in the temporary table
>      - commit the transaction
>
> 6. Delete the temporary table


Thanks, will consider this also.

--
--
Yusuf Zorlu | MicrotronX

Yusuf,

<< TIM: What about adding such a functionality in v3? >>

It's not necessary.  If you lock the master record before starting the cached updates on the detail records, and that is the *only* way that a user can get at the detail records, then you're all set.

However, typically, I recommend that you wrap the ApplyCachedUpdates in a transaction so that they are guaranteed to succeed/fail as a unit:

MyDatabase.StartTransaction;
try
  MyDetailTable.ApplyCachedUpdates;
  MyDatabase.Commit.
except
  MyDatabase.Rollback;
  raise;
end;

Tim Young
Elevate Software
www.elevatesoft.com