Database standards OR Am I doing this right?

[MarkMLl]

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 
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

[cdbc]

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

[TRon]

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]

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...

[MarkMLl]

@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