Forum > Databases

Access mysql in a multithread environment

(1/2) > >>

I have something like this:

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---// at the start of the programMainSQLConnection := tMYSQL57Connection.Create(nil);MainSQLTransaction:=tSQLTransaction.Create(nil);with MainSQLConnection do  begin  DataBaseName := 'dbname';  HostName := 'localhost';  Port := 3306;  UserName := 'username';  Password := '';  end;MainSQLConnection.Connected:=true;MainSQLTransaction.Database := MainSQLConnection; // many times during the programwith tSQLQuery.Create(nil) do  begin  Transaction := MainSQLTransaction;  SQL.Text := 'select * from table;';   try    Open;    if Eof then writeln('No record');    // also some update, etc.    MainSQLTransaction.CommitRetaining;  finally;    Close;    Free;    end;  end; // at the end of the programMainSQLConnection.Close;MainSQLTransaction.Free;MainSQLConnection.Free;    
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"

I would give each thread its own transaction.

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?

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

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.



[0] Message Index

[#] Next page

Go to full version