Hi,

I would just like to clear up in mind once and for all when deleting an item
that has a master-detail relationship.  Take the following:

MasterTable is called Roster and has a key of Code

ClientTable1 is called Booking and has foreign key of RosterCode
ClientTable2 is called History and has foreign key of RosterCode

I want to delete an entry from the MasterTable.  In the client tables I want
to clear the link - NULL (foreign key) and ensure these are successfully
cleared before deleting the mastertable entry .  Under normal circumstances
this is fine.  However the Booking or History table *could* be in edit mode
due to another user editing that record.  In this instance we do not want to
allow the deletion of the entry in MasterTable unless all links in client
tables can  be cleared.

As I understand transactions could be used, and only if ALL records were
modified would the transaction be committed.  However, transactions require
exclusive access to the *whole* table which is almost impossible in this
multi-user application.

What are my alternatives?  I must have pessimistic locking on the booking
table.  Using V3.30

Thanks

Alex

Al Vas

If I understand your question...

If the tables are in edit mode (one or both tables) then transaction will wait the users to unlock the records (via
post or cancel) and then perform the task.
IOW, you do not need to do anything else like the code below:

try
  Database.StartTransaction;
  // Delete/update foreign keys from DetailTable
  // Delete the record in the MasterTable
  Database.Commit;
except
  // The code will be here if transaction time was so long
  Database.RollBack;
end;

Eduardo

Hi Eduardo,

Thanks for your reply.  So in essence a StartTransaction does *not* require
a lock on the whole table?  I was under the impression in previous
conversation that it did require this.

Regards

Alex

"Eduardo" <contato@hpro.com.br> wrote in message
news:480B5BB2-F210-4A8B-A921-857777C4C1A6@news.elevatesoft.com...
> Al Vas
>
> If I understand your question...
>
> If the tables are in edit mode (one or both tables) then transaction will
> wait the users to unlock the records (via
> post or cancel) and then perform the task.
> IOW, you do not need to do anything else like the code below:
>
> try
>   Database.StartTransaction;
>   // Delete/update foreign keys from DetailTable
>   // Delete the record in the MasterTable
>   Database.Commit;
> except
>   // The code will be here if transaction time was so long
>   Database.RollBack;
> end;
>
> Eduardo
>

Al Vas

> Thanks for your reply.  So in essence a StartTransaction does *not*
> require a lock on the whole table?  I was under the impression in previous
> conversation that it did require this.
No. The StartTransaction will wait until get a lock on the whole table.
It gives an error only if this wait time will be so long.

Eduardo

Alex,

<< Thanks for your reply.  So in essence a StartTransaction does *not*
require a lock on the whole table?  I was under the impression in previous
conversation that it did require this. >>

It requires a lock on the whole table, however the lock only blocks writes
(Posts), not reads and record locks.  As long as you keep the transaction
fairly short, it will not hamper the concurrency in the system.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim,

Just to confirm, if it is a table that is used *alot* and is most likely
constantly in edit mode by many different users, it will *not* need to wait
for no edits before being able to complete the transaction?

Alex

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:F6167CF7-E0E3-42F0-90CE-2DA9CB41CF2A@news.elevatesoft.com...
> Alex,
>
> << Thanks for your reply.  So in essence a StartTransaction does *not*
> require a lock on the whole table?  I was under the impression in previous
> conversation that it did require this. >>
>
> It requires a lock on the whole table, however the lock only blocks writes
> (Posts), not reads and record locks.  As long as you keep the transaction
> fairly short, it will not hamper the concurrency in the system.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Al Vas

If this table is used *a lot* then you should think in using Optimistic
Locking model. If you are using pessimistic locking, then when the users are
editing a record then this record is locked then transaction will wait these
users post or cancel the operation before continue the process. But if this
time is too long, you will receive an error message.

Eduardo.

Alex,

<< Just to confirm, if it is a table that is used *alot* and is most likely
constantly in edit mode by many different users, it will *not* need to wait
for no edits before being able to complete the transaction? >>

Correct.  It only has to wait for any current Posts (writes), not any edits
in progress where users are fussing with a locked record buffer on the
client.  However, please be aware that if the transaction has to modify or
delete any record that is already being edited by another user or session,
then the session executing the transaction will get a record lock error if
it can't lock the record.  You can then choose to retry the update or
delete, or simply rollback the transaction.

--
Tim Young
Elevate Software
www.elevatesoft.com