At 03:56 PM 31-05-01 +0000, you wrote:

>I'm completely lost with tthe locking behavior on a master-detail
>relationship. I have tried to work with LockSQL but I get
>always the message "Record locked by another user" even with an unique
>user.

This seems to indicate that you have more than one transaction involving the same records in an uncommitted state. The deadlock indicates this too. If this is happening even with a unique user, then your application is failing to either commit or rollback before attempting to start a fresh transaction involving the same set of data.

>If I leave the lockSQL properly blank, locking works for the
>current detail register with the message "Deadlock" but it does not
>work if you try to update other line, no locking is performed on the
>master table.

Locking is always performed on any tables for which you have pending updates. In the normal course of events, you are not aware of it. "Pessimistic locking" as implemented in LockSQL, for example, is a "fudge" that puts a table into this "update pending" state and prevents any other transaction from selecting it.

If you think you need pessimistic locking, apply a LockSQL statement to your master table ONLY. Then your transaction will have exclusive access to the master AND the dependent detail rows until you either commit or roll back the transaction.

Things could get complicated if your application is using different master tables for the SAME detail rows. Is this the case?

>I have tried also to work with KeyLinks but I could not afford to put
>a "correct" syntax in that field. Each time I put a different table
>the query editor gives an error, something like "unproper field".

I don't understand this statement at all. The Keylinks for the master set should be the primary key of the master table. The Keylinks for the details set should be the primary key of the details table.

You also need Mastersource and Masterlinks on the details table.

Please explain this bit about "each time I put a different table". Are you swapping different datasets in and out of your query components?

>I used to work with BDE and I locked the master table with a
>"Master.Edit" when the user was working with the lines. Should be done
>the same way here ? Is there any better way?

If the Keylinks, Mastersource and Masterlinks are properly set up, just having the master set in edit mode will isolate your sets. Did you work with InterBase and the BDE or was it a desktop database you used?

>At the end I get pessimistic locking working for detail or master but
>no both. Is this the correct way of proceeding?

No: if you MUST use pessimistic locking, you need it only on the master set, as explained earlier...but you need to say whether your situation is complicated by have multiple masters for a single detail set.

>I have the starting guide of IBObjects and there is neither a specific
>policy nor some guideline to be followed. If somebody can advise me
>about any documentation I will be very pleased.

Read the section about Master-Detail relationships in "Thinking About Links". You might also benefit from reading the chapters on Transactions and try to understand the way the different isolation levels of the transactions work and why, except in extreme concurrency conditions, you don't need pessimistic locking.

There is a TI-sheet coming up on the subject of M-D but it's not ready yet.

Regards,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________