Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 8 of 8 total |
Transactional Deletion |
Sat, Nov 11 2006 9:51 PM | Permanent Link |
"Al Vas" | 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 |
Sun, Nov 12 2006 3:25 PM | Permanent Link |
Eduardo | 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 |
Sun, Nov 12 2006 8:50 PM | Permanent Link |
"Al VAs" | 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 > |
Mon, Nov 13 2006 4:01 AM | Permanent Link |
"Jose Eduardo Helminsky" | 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 |
Mon, Nov 13 2006 5:12 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Mon, Nov 13 2006 6:25 PM | Permanent Link |
"Al Vas" | 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 > |
Tue, Nov 14 2006 4:03 AM | Permanent Link |
"Jose Eduardo Helminsky" | 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. |
Tue, Nov 14 2006 8:52 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |