Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 9 of 9 total |
EDB with pessimistic locking and cached updates |
Fri, Nov 29 2019 1:28 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | 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 |
Fri, Nov 29 2019 3:39 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Yusuf
My personal approach is to use a transaction. Roy Lambert |
Fri, Nov 29 2019 6:39 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | 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 |
Fri, Nov 29 2019 10:00 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sat, Nov 30 2019 10:46 AM | Permanent Link |
Fernando Dias Team Elevate | 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] |
Sun, Dec 1 2019 3:34 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Fernando
That's about it. Roy Lambert |
Mon, Dec 2 2019 1:28 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | 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 |
Mon, Dec 2 2019 1:29 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | 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 |
Mon, Dec 9 2019 12:36 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |