Lars Fosdal

Thanks for sharing the root cause! Such mysteries can be so hard to track down! I've seen this before on Windows - but didn't make the connection this time, if you pardon the pun. Does an Android service have a Console define? Our FireDAC helper unit uses that to handle Windows services and other console apps. unit PSD_db_FireDAC; /// <summary> Generic FireDAC wrapper. Use PSD_db_FireDAC_MSSQL and PSD_db_FireDAC_PostgreSQL units </summary> interface uses System.Classes, System.SysUtils, Winapi.Windows, System.Variants, System.AnsiStrings, System.TimeSpan, System.Diagnostics, System.TypInfo, System.Math, StringFunctions, TineInterfaces, TineClasses, PSDResourcePool, Generics.Defaults, Generics.Collections, PSD_Db_Abstract, PSDConstants, tiConsts, PSDLogEvent, PSDLogDrivers, PSDConnectivityStatus, Data.DB, FireDAC.Stan.Intf, PSDStatisticsEngine, {$ifdef Console} FireDAC.ConsoleUI.Wait, {$else} FireDAC.VCLUI.Wait, FireDAC.Comp.UI, {$endif} FireDAC.UI, FireDAC.UI.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.Phys.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.Stan.Param, FireDAC.DApt, FireDAC.comp.DataSet, FireDAC.comp.Client, FireDAC.comp.Script, FireDAC.Moni.Base, FireDAC.Moni.RemoteClient, FireDAC.Moni.FlatFile;

Defeatist? More like a realist. After getting rid of the per field micro locking and properly handling contention, the problem was greatly reduced. Even so - carefully crafted sins appear from time to time. Such is the life of dealing with legacy code.

My apologies for being unclear: One crit sec per object instance, with separate locking per object field. Return to previous description.

Can you reproduce the problem in a bare minimum app?

The deadlock on single lock instance for multiple shared instances (read; individual access locking to multiple fields of a class) is most likely to happens when multiple fields for multiple objects are accessed from multiple threads for the same objects. In our case, it was parallel processing of warehouse picks. F.x. Same article, same location, different picker, needing to update the stock saldo at the same time. It involves interaction between a relatively large number of object instances, such as object cache list retrieval and the objects themselves. I could exemplify in detail, but then I'd only get "but you are doing it wrong" - which I was - and that is why I work hard to minimize the use of implicit locking per field, and rather lock the entire object when needed for as short a time as possible, and avoid locking as far as can be deemed safe. This is not academically flawless code. It is code developed by a multitude of people over a long time.

Is there any design time settings on any of the db components that may be in violation at time of startup?

Here is another unhelpful blanket statement, based on real-world, empirical evidence. Multiple threads, multiple objects, cross-thread cross-object access and micro locking per field using the same single lock per object = high risk of deadlock. Sure, you can attribute it to bad design - but perfect designs for software that evolves over decades are really hard to do.

At what point is the instance pointer reference for the string, interface or dyn.array changed? When are field values in instances not aligned (unless overrides have been added)? https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/lock-statement

Is it a rights issue? Can the app lack access to the location where the db is supposed to be?

Naturally, it is a simplification and doesn't apply to every context, but we used to have lots of micro locking, and lots of lock failures. The problem was multiple threads competing over multiple objects over multiple fields from the same objects for read access. Locking per field behaved like a "zipper" between threads. Think Allocation / Article / Saldo / Location - multiple allocations of the same article at the same location. Object locking with wait/retry on contention solved that for us. You could argue that MRSW locking would be a better approach, but that is its own can of worms if you need to increase lock level. A more important rule, is do not lock unless you really need to lock. Analyze the way the code works. Straight assignments are atomic, unless they involve logic or reallocation. Will there REALLY be other threads trying to access the same data at the same time? Do the data change so frequently and are they accessed so frequently that it is a real problem?

Never do locking on a per field basis - unless you have one critical section per field. Otherwise, always lock the entire object. As Georgge says: The risk of deadlock is real. Been there, done that. if ipUser.TryLock then try ipUser.Name := 'New name' finally ipUser.Unlock; end else // do a sleep/retry?

Use the FireDAC Monitor to check the operations sent to the DB. Can it be type related? What field type is mYear, what type is cYear? Or - looking at the error message. Is the connection actually open? Is the connection or dataset used elsewhere?

Indeed I do. Size 12 on 32" 4K display, Windows 100% UI scaling. The uneven height of some of the lowercase letters on JetBrain was the first part of the problem. The second problem was that the font was to "heavy" i.e. the normal was too close to what I consider a bold font.

Actually, I wasn't thinking of OTL at all - but that would be worth looking into. I attached a generic scheduler though. I had to clean out some proprietary code, but not much. The idea is that the generic type T is an enumerated type like f.x. TJob = (jobUnknown, jobUpdateFiles, jobPublishPage, jobCleanupOldData, jobEtcEtcEtc); The actual task manager is a different story. It would take me quite some time to "unproprietaryize" that one - and that is really where the work is. GenericScheduler.pas

When you put it that way, I wonder why I had the notion that a remote TCP host's willful disconnect actually signaled the connected.

Write a simple scheduler - i,e, a collection of tasks and when / how often you want to run them. Oneshot immediately, oneshot at a specific time, repeating by interval, repeating at fixed points in time, etc. Create a task manager thread that checks the scheduler and queues tasks into a task execution queue. You can have a framework of rules such as tasks not being able to run in parallel, or tasks that should not be queued if already in the queue or being executed, etc. The task manager thread also start the tasks from the execution queue and launch them as separate threads at the appropriate time. You decide how many parallel tasks you can run at a time, and you could even have a task worker thread pool.

I still think I prefer Source Code Pro.

I've had the socket open doing writes and reads. I still keep the connection open as I intend to send more shortly. If the remote point then disconnects gracefully - should I not be notified?

The reason is that I seem to get it without having had a disconnect event. If I have had observed the disconnect, I obviously would not try to send data on a closed socket.

@Hans-Wolfgang - The official definition of classes can be found here: http://docwiki.embarcadero.com/RADStudio/Rio/en/Classes_and_Objects_(Delphi) If you look at the left side of the above mentioned page, there is also a link to switch to German. Or use this link: http://docwiki.embarcadero.com/RADStudio/Rio/de/Klassen_und_Objekte_(Delphi)

What about EIdConnClosedGracefully? That is a kind of an annoying one.

TRec<T> = record value: T; procedure Init; end; procedure TRec<T>.Init; begin Value := Default(T); end; TRec<Integer> = record value: Integer; // Default(T) = 0 end; TRec<TObject) = record value: TObject; // Default(T) = NIL end; // Assuming nullables would look like the C# equivalents TRec<Integer?> = record value: Integer?; // Default(T) = NULL end; TRec<TObject?) = record value: TObject?; // Default(T) = NULL end; For nullable object references - will we have a non-NULL that is nil? I guess the C# solution makes sense, although there is a difference between NULL (never assigned) and nil ( known non-reference assigned).

You know me. I am always in a state of bewilderment 🙂 Based on your updated sync requirements description, I'll stop envisioning a new generation of your system, and instead tell you about how I solved a similar need for synchronization. I did distributed stock, energy and weather market data databases for well over a decade. The solution was database agnostic and the central server generated change instructions - which technically were binary scripts for adding, changing or removing data. The change scripts were sequenced in chronological order by changes done at the central server, so that to do a refresh, the client requested an update from the last synch point, and then convert the list of change instructions to operations relevant to the local database. The change instruction format remained unchanged even though the underlying databases changed from flat files, to client side Paradox, to server side sql over a tcp connection. I still shudder at the memory of having terabytes of Paradox tables that I couldn't touch except through the change instructions 😛 Today, these data would have been retrieved through a REST interface.

I liked the good old RESTful Web Services by Leonard Richardson and Sam Ruby on O'Reilly Media - although it is a bit web-centric. It is indeed complex. Some times peek/poke is the right way, while at other times you do want structured "batch" updates. The design needs to be governed by the nature of the API usage, the amount of concurrent use, the complexity of the data.

This is going to get interesting if we get nullable generic types.