Recent

Author Topic: Multiple Access To Data  (Read 2766 times)

StephenMilner

  • New Member
  • *
  • Posts: 15
Multiple Access To Data
« on: January 17, 2022, 04:11:37 pm »
Many years ago I was involved in a project that developed till software using Kylix3 and data enabled components.

I forget the exact details but we found a situation where:

One till read a stock figure for an item.
Another till read the same stock figure for the same item.
The first till decremented the stock figure by a number and posted the change
The second till decremented the original stock figure by a number and posted the change.

This resulted in an incorrect stock figure.

There were some events for the data components in Kylix which signalled to the data component that the underlying stock position on the database had changed and some code could be written to make sure the data was re-read before decrementing the stock. Admittedly, for Firebird, the events didn't trigger correctly and required some work to re-write the components to fix them first.

Do similar events exist on the FPC/Lazarus data components?

Regards,
Stephen

Zvoni

  • Hero Member
  • *****
  • Posts: 1176
Re: Multiple Access To Data
« Reply #1 on: January 17, 2022, 04:14:53 pm »
Sounds like race-conditions resp. a missing Write-Lock on the recordset
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

StephenMilner

  • New Member
  • *
  • Posts: 15
Re: Multiple Access To Data
« Reply #2 on: January 17, 2022, 04:27:02 pm »
The problem with locking is that it isn't perfect when user input is required.

Normally I'd:
Read and Lock
Change
Write and Unlock

In a till example though you've got:
Read (and if locked there's a problem if someone else want's to get the same item's data)
User spends some time making a change
Write

IIRC it was something to do with the update type of the data set > Update where all fields matched the old fields before writing and if they didn't possibly threw an exception or triggered an event. Also IIRC there was some problem when the exception or event triggered: the old field value wasn't accurate, possibly having been overwritten by the new field.

For an example such as the till one above I could probably write increment/decrement functions that actually perform the quick, read & lock, modify, post & unlock and I can't remember at the time why we didn't do this.

Regards,
Stephen

MarkMLl

  • Hero Member
  • *****
  • Posts: 4154
Re: Multiple Access To Data
« Reply #3 on: January 17, 2022, 07:25:28 pm »
Check what transaction isolation levels your backend database supports.

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

StephenMilner

  • New Member
  • *
  • Posts: 15
Re: Multiple Access To Data
« Reply #4 on: January 18, 2022, 10:19:29 am »
Check what transaction isolation levels your backend database supports.

MarkMLl

That's not something I've decided yet, so I can factor it in.

Thaddy

  • Hero Member
  • *****
  • Posts: 11516
Re: Multiple Access To Data
« Reply #5 on: January 18, 2022, 10:37:19 am »
Check what transaction isolation levels your backend database supports.

MarkMLl

That's not something I've decided yet, so I can factor it in.
It is not "factor it in", it is the core question you should focus on. Locking should be done at the database level, not program level, except if you use a very ancient database that does not support locking. Your program needs to just check for locks before a commit.
« Last Edit: January 18, 2022, 10:40:24 am by Thaddy »
Путин преступник. Россияне дезинформированы.

MarkMLl

  • Hero Member
  • *****
  • Posts: 4154
Re: Multiple Access To Data
« Reply #6 on: January 18, 2022, 11:51:26 am »
... except if you use a very ancient database that does not support locking.

In which case it's time to select a more recent database. PostgreSQL (as a specific example) has had proper transaction isolation for 20+ years.

/However/, using a backend database properly does imply that queries are issued and completed promptly, and this is not well-supported by RAD-style environments which assume that a connection can be held open indefinitely. Arguably, this has been responsible for the proliferation of "modern" browser-based apps which handle as much as possible in local scripting.

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

Thaddy

  • Hero Member
  • *****
  • Posts: 11516
Re: Multiple Access To Data
« Reply #7 on: January 18, 2022, 01:31:18 pm »
@Mark
Note most of the time I do not use Lazarus at all for db backend interfacing, so not RAD. (But I do use Lazarus)
Путин преступник. Россияне дезинформированы.

 

TinyPortal © 2005-2018