Access mysql in a multithread environment

[jollytall]

I have something like this:

Code: Pascal  [Select][+][-]

1. // at the start of the program
2. MainSQLConnection := tMYSQL57Connection.Create(nil);
3. MainSQLTransaction:=tSQLTransaction.Create(nil);
4. with MainSQLConnection do
5.   begin
6.   DataBaseName := 'dbname';
7.   HostName := 'localhost';
8.   Port := 3306;
9.   UserName := 'username';
10.   Password := '';
11.   end;
12. MainSQLConnection.Connected:=true;
13. MainSQLTransaction.Database := MainSQLConnection;
14.  
15. // many times during the program
16. with tSQLQuery.Create(nil) do
17.   begin
18.   Transaction := MainSQLTransaction;
19.   SQL.Text := 'select * from table;';
20.  
21.   try
22.     Open;
23.     if Eof then writeln('No record');
24.     // also some update, etc.
25.     MainSQLTransaction.CommitRetaining;
26.   finally;
27.     Close;
28.     Free;
29.     end;
30.   end;
31.  
32. // at the end of the program
33. MainSQLConnection.Close;
34. MainSQLTransaction.Free;
35. MainSQLConnection.Free;  
36.  

Two questions:
1. Is it OK like this? So far it works well, but I am not sure if the sequence of creating and closing/freeing the Connection and the Transaction are correct.
2. How to place the middle part in a thread, thread-safe? I have many threads that want to use the same code in the middle. I do not want to make my own CriticalSections to make it thread safe, SQL should do that. Can I keep both the Connection and the Transaction global, or shall I make Connection global and Transaction per thread, or shall I make both in the thread. My concern is that threads are coming and going, so I would like to minimise the creation and destruction of SQL elements (especially the slow Connect) in every thread. When I run it today, it sometimes work, but often get strange errors, like "Cannot open a non-select statement" when the SQL.Text IS a select statement, or "Commands out of sync; you can't run this command now"

[SymbolicFrank]

I would give each thread its own transaction.

[jollytall]

I tried that, but it seems even that is not enough.
I consulted some non-pascal experts and I got the hint (might be different in fpc) that the Connection is the actual "SQL terminal" and the transaction is within that. So, if two threads start a transaction, they compete in the same "window", i.e. they can still get competing. I was told the safe way is to give each thread a separate connection as well.
I tried that, and it works so-far, but then the connection is slow to make for each thread. The suggestion is to make a pool of Connections and use a free one, but that again has to be Mutexed not to assign the same Connection to two threads in a narrow race condition.

Is there a better way?

[zeljko]

IMO - no. Each thread must have it's own connection.

[MarkMLl]

I think I've seen this discussed in the context of other database backends in the past, and I think that the consensus was that since the underlying FPC libraries are based on OS-level handles etc. connections and transactions were accessible across threads /but/ were not necessarily thread-safe (i.e. standard race hazards etc.). However interaction with the Lazarus/LCL components obviously must obey the standard rules for visual components, i.e. the background thread must never try to change their state directly and should be very careful about reading it.

MarkMLl

[jollytall]

In the last month I did a lot of research and use it a lot. What I learnt:
Years ago (actually it was my question then) it was solved by PascalDragon. The standard libmysqlclient library (.a or .o) is not thread safe. There is a libmysqlclient_r version what is. Using InitialiseMySql the desired library can be selected.
Now, I have mariadb and it has libmariadbclient, but not the _r version. This should not be a problem, as I use a separate connection and transaction for every thread separately.

But it raised another problem that I cannot solve.
I am very careful to call the last three lines of my code earlier (Close, Free, Free) every time the thread creates and opens a connection. Still, whenever I exit my program, I get a system error:
Error in my_thread_global_end(): xxx threads didn't exit.

Reading C++ forums, they recommend to call mysql_thread_end(), but I do not find an FPC equivalent for that.

Any idea how to make many Connections and Transactions (1 pair per thread maximum - if needed) and still do not leave unfinished threads?

(Btw. in top I do not see the high number of threads neither under my process, nor under mysqld)