Recent

Author Topic: Database standards OR Am I doing this right?  (Read 2467 times)

MarkMLl

  • Hero Member
  • *****
  • Posts: 6947
Re: Database standards OR Am I doing this right?
« Reply #15 on: April 20, 2024, 10:57:58 am »
Hi
@TRon: Primoz Gabrielski once wrote an interesting article in "The Delphi Magazine", about 'File-based Locking'.
Would have to sift through all the old magazines to find it, though  :P
Regards Benny

I'd strongly recommend against trying to use file-based locking for this, particularly if there are other forms of locking available at the API or SQL level ("transaction isolation").

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

cdbc

  • Hero Member
  • *****
  • Posts: 1253
    • http://www.cdbc.dk
Re: Database standards OR Am I doing this right?
« Reply #16 on: April 20, 2024, 11:17:40 am »
Hi
@MarkMLl: I agree, the best solution comes from the database itself. At least I assume that, they've put a lot more work and knowhow into the database-code, than I'll ever do with my client-code...
That poat was only a sidenote to TRon's post...
Regards Benny
If it ain't broke, don't fix it ;)
PCLinuxOS(rolling release) 64bit -> KDE5 -> FPC 3.2.2 -> Lazarus 2.2.6 up until Jan 2024 from then on it's: KDE5/QT5 -> FPC 3.3.1 -> Lazarus 3.0

TRon

  • Hero Member
  • *****
  • Posts: 2804
Re: Database standards OR Am I doing this right?
« Reply #17 on: April 20, 2024, 11:28:02 am »
That poat was only a sidenote to TRon's post...
.. and one that I completely missed. My apologies.

Yes, it is a good addition and I am aware that you can do it that way albeit better not in the context of TS's post (indeed better let the database engine/API take care of that for us).

gidesa

  • Jr. Member
  • **
  • Posts: 87
Re: Database standards OR Am I doing this right?
« Reply #18 on: April 20, 2024, 02:16:21 pm »
The concurrent access to db from different users is one of the first and most important problem that a dbms has to resolve.
So, it's no surprise that it's much better to use dbms concurrency mechanism (logical transaction, lock, isolation level, etc.), than handmade limited solutions.
Initially there is an effort to understand that mechanism, yes, but it's well worth.

I agree, the best solution comes from the database itself. At least I assume that, they've put a lot more work and knowhow into the database-code, than I'll ever do with my client-code...
« Last Edit: April 20, 2024, 02:57:51 pm by gidesa »

MarkMLl

  • Hero Member
  • *****
  • Posts: 6947
Re: Database standards OR Am I doing this right?
« Reply #19 on: April 22, 2024, 10:22:34 am »
@MarkMLl: I agree, the best solution comes from the database itself. At least I assume that, they've put a lot more work and knowhow into the database-code, than I'll ever do with my client-code...
That poat was only a sidenote to TRon's post...

I did tack on a couple of additional points but was in a rush to get out and it didn't get posted properly.

The first is that while transaction isolation is the "correct" way to go, how to actually set it up is not very well standardised from the POV of a client API and one might find onesself having to use a text parameter:

Code: Pascal  [Select][+][-]
  1.     SQLTransaction1.Params.Clear;
  2.     scratch := '';
  3.     if ListBoxIsolation.ItemIndex >= 0 then
  4.       scratch := '''' +  Trim(ListBoxIsolation.Items[ListBoxIsolation.ItemIndex]) + '''';
  5.     if scratch <> '' then
  6.       SQLTransaction1.Params.Append('default_transaction_isolation=' + scratch);
  7.     SQLQuery1.Open;                     (* Remaining components auto-activated  *)
  8.  

The second is that graphical database controls are fundamentally incompatible with long-running transactions: if there's e.g. a network glitch during the lifetime of a transaction the application program has no way of recovering. I explored that in conjunction with PostgreSQL at https://github.com/MarkMLl/testdb/tree/main, and since my findings were basically the same as those reported by somebody running a different backend I anticipate that they will be much the same for everything.

That was, TBH, something I'd anticipated for a long time but hadn't actually experimented with. The problem is that SQL was originally a "mainframe thing", and the designers anticipated that it would be used by "middleware" rather than directly by a remote client application.

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

 

TinyPortal © 2005-2018