Database Components in Multi-threaded App

Hello all,

I am currently changing a multi-threaded app that I have that processes alot of
data and assembles an XML with it. The new version will add the resulting data
to a Database Table. I found that my app is dead locking (probably) at some
point when I use more than one thread to write to the Database Table. If I use
only one writing thread, everything is OK. If I start using more than one, it
hangs.
Each writing thread has it's own IB database components (in my case an
IBDatabase, IBTransaction, IBStoredProcedure and another IBTransaction). Before
doing that I was tyring with a TDataModule and using it's functions to do the
dirty work (again, each Thread had it's own DataModule).
I thought that by having a separate set of database components for each thread
would eliminate me having to worry about race conditions..(apparently not!).
Can anyone tell me what would be the best way to implement this
multi-writing-threads to the same Database Table?

Thanks in advance,

Are the threads just inserting new records or are they updating
existing records?

What error message(s) do you get?

What error message(s) appear in interbase.log?

Multiple threads updating the table is no different than multiple users
updating the same table. There should not be a problem unless one
thread tries to update a row that was changed by another transaction
that is still active.

How do you use the two transaction components?

When are transactions committed?

-- 

> I am currently changing a multi-threaded app that I have that processes
alot of data and assembles an XML with it. The new version will add the
resulting data to a Database Table. I found that my app is dead locking
(probably) at some point when I use more than one thread to write to the
Database Table. If I use only one writing thread, everything is OK. If I start
using more than one, it hangs.
> Each writing thread has it's own IB database components (in my case an
IBDatabase, IBTransaction, IBStoredProcedure and another IBTransaction). Before
doing that I was tyring with a TDataModule and using it's functions to do the
dirty work (again, each Thread had it's own DataModule).
> I thought that by having a separate set of database components for each
thread would eliminate me having to worry about race conditions..(apparently
not!).
> Can anyone tell me what would be the best way to implement this
multi-writing-threads to the same Database Table?

Sounds not that bad. Do you use a TCP/IP connection, even if it is a 
local database?


-- 
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions

Hi,

I use TCP/IP to connect to the database, even though I am testing it on a local
machine (in production this won't be necessarily true).

>Are the threads just inserting new records or are they updating
>existing records?

Just inserting new records.

>What error message(s) do you get?

None. The app just hangs (very dead-lock typical).

>What error message(s) appear in interbase.log?

XXXX (Client)	Wed Mar 12 13:31:52 2008
	INET/inet_error: connect errno = 10060

XXXX (Client)	Wed Mar 12 13:32:34 2008
	INET/inet_error: connect errno = 10060

XXXX (Client)	Wed Mar 12 13:33:16 2008
	INET/inet_error: connect errno = 10060

It's actually a Firebird.log.

>Multiple threads updating the table is no different than multiple users
>updating the same table. There should not be a problem unless one
>thread tries to update a row that was changed by another transaction
>that is still active.

That's exactly what I thought before deciding on this approach.

>How do you use the two transaction components?

I don't use the Database transaction explicitly. I just connect to the Database
using Open. And close it down using CloseDataSets; and then Close.

>When are transactions committed?

I have a WriteResult function that's like this:
begin
  if FSPAddRsltTRX.InTransaction then
    FSPAddRsltTRX.Rollback;

  FSPAddRsltTRX.StartTransaction;
  try
    FSPAddRslt.ParamByName('EDITED').AsInteger := 'EDITED;
    FSPAddRslt.ParamByName('EDITED').AsInteger := 'EDITED;
    FSPAddRslt.ParamByName('EDITED').AsInteger := 'EDITED;
    FSPAddRslt.ParamByName('EDITED').AsString := 'EDITED;
    FSPAddRslt.ParamByName('EDITED').AsBlob := 'EDITED;
    FSPAddRslt.ParamByName('EDITED').AsInteger := 'EDITED;
    FSPAddRslt.ExecProc;
  finally
    FSPAddRsltTRX.Commit;
  end;
end;

Each thread calls this function a number of times. It's a local function of each
thread so I assume there's no issues in here also.

FSPAddRslt and TRX are created in the context of the owner thread (on the Create
method). And destroyed in the Destroy method. Could this be causing the
miss-behaviour?

I don't know if this helps but if I create a DataModule descendant to handle
this (with one copy of the DM / thread) the threads deadlock/wait forever in the
DataModule.Create method.

Thanks for the help,

Hi,

I found the problem. It was on a hardcoded DatabaseFileName that didn't contain
the ip address before the actual file path and thus making the Database access
it on FileMode instead of TCPIPMode locking my app down :/

Thanks for all your help and sorry about that,

Marcelo Grossi

"Marcelo Grossi" <nospam@please.com> wrote:
>
>Hi,
>
>I use TCP/IP to connect to the database, even though I am testing it on a
local machine (in production this won't be necessarily true).
>
>>Are the threads just inserting new records or are they updating
>>existing records?
>
>Just inserting new records.
>
>>What error message(s) do you get?
>
>None. The app just hangs (very dead-lock typical).
>
>>What error message(s) appear in interbase.log?
>
>XXXX (Client)	Wed Mar 12 13:31:52 2008
>	INET/inet_error: connect errno = 10060
>
>XXXX (Client)	Wed Mar 12 13:32:34 2008
>	INET/inet_error: connect errno = 10060
>
>XXXX (Client)	Wed Mar 12 13:33:16 2008
>	INET/inet_error: connect errno = 10060
>
>It's actually a Firebird.log.
>
>>Multiple threads updating the table is no different than multiple users
>>updating the same table. There should not be a problem unless one
>>thread tries to update a row that was changed by another transaction
>>that is still active.
>
>That's exactly what I thought before deciding on this approach.
>
>>How do you use the two transaction components?
>
>I don't use the Database transaction explicitly. I just connect to the
Database using Open. And close it down using CloseDataSets; and then Close.
>
>>When are transactions committed?
>
>I have a WriteResult function that's like this:
>begin
>  if FSPAddRsltTRX.InTransaction then
>    FSPAddRsltTRX.Rollback;
>
>  FSPAddRsltTRX.StartTransaction;
>  try
>    FSPAddRslt.ParamByName('EDITED').AsInteger := 'EDITED;
>    FSPAddRslt.ParamByName('EDITED').AsInteger := 'EDITED;
>    FSPAddRslt.ParamByName('EDITED').AsInteger := 'EDITED;
>    FSPAddRslt.ParamByName('EDITED').AsString := 'EDITED;
>    FSPAddRslt.ParamByName('EDITED').AsBlob := 'EDITED;
>    FSPAddRslt.ParamByName('EDITED').AsInteger := 'EDITED;
>    FSPAddRslt.ExecProc;
>  finally
>    FSPAddRsltTRX.Commit;
>  end;
>end;
>
>Each thread calls this function a number of times. It's a local function of
each thread so I assume there's no issues in here also.
>
>FSPAddRslt and TRX are created in the context of the owner thread (on the
Create method). And destroyed in the Destroy method. Could this be causing the
miss-behaviour?
>
>I don't know if this helps but if I create a DataModule descendant to handle
this (with one copy of the DM / thread) the threads deadlock/wait forever in the
DataModule.Create method.
>
>Thanks for the help,
>
>Marcelo Grossi