[Lazarus] Multithreaded app with PostgreSQL connection

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[Lazarus] Multithreaded app with PostgreSQL connection

Ondrej Pokorny
Hello!

I have a multithreaded application where every thread needs to connect
to the same PostgreSQL database. How to design the DB connectivity for a
multithreaded application correctly?

I see that TPQConnection has an FConnectionPool. So is it thread-safe?

Create a TPQConnection in the main thread; create
TSQLQuery+TSQLTransaction in a (different) thread and connect it to to
the TPQConnection from main thread via the Database property?
Or do I need a TPQConnection per thread?

I somehow failed to find any information about sqldb and multithreading:
http://www.freepascal.org/docs-html/fcl/sqldb/index.html
http://wiki.freepascal.org/SQLdb_Programming_Reference

Thanks :)
Ondrej

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Multithreaded app with PostgreSQL connection

Michael Van Canneyt


On Mon, 20 Jun 2016, Ondrej Pokorny wrote:

> Hello!
>
> I have a multithreaded application where every thread needs to connect
> to the same PostgreSQL database. How to design the DB connectivity for a
> multithreaded application correctly?

Best is to have every thread use it's own connection.

If you want only a single connection, then you should make sure all Database
and Transaction properties (plus setting them to Nil) are protected by a
critical section: there are non-threadsafe lists involved.

>
> I see that TPQConnection has an FConnectionPool. So is it thread-safe?

Not if you don't follow the above steps.

>
> Create a TPQConnection in the main thread; create
> TSQLQuery+TSQLTransaction in a (different) thread and connect it to to
> the TPQConnection from main thread via the Database property?
> Or do I need a TPQConnection per thread?

Yes.

>
> I somehow failed to find any information about sqldb and multithreading:
> http://www.freepascal.org/docs-html/fcl/sqldb/index.html
> http://wiki.freepascal.org/SQLdb_Programming_Reference

Because it is not thread safe. It is on my todo list.

Michael.
--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Multithreaded app with PostgreSQL connection

Mark Morgan Lloyd
Michael Van Canneyt wrote:

> On Mon, 20 Jun 2016, Ondrej Pokorny wrote:
>> Hello!>> I have a multithreaded application where every thread needs
>> to connect > to the same PostgreSQL database. How to design the DB
>> connectivity for a > multithreaded application correctly?
> Best is to have every thread use it's own connection.
> If you want only a single connection, then you should make sure all
> Databaseand Transaction properties (plus setting them to Nil) are
> protected by acritical section: there are non-threadsafe lists involved.
>>> I see that TPQConnection has an FConnectionPool. So is it thread-safe?
> Not if you don't follow the above steps.
>>> Create a TPQConnection in the main thread; create >
>>> TSQLQuery+TSQLTransaction in a (different) thread and connect it to
>>> to > the TPQConnection from main thread via the Database property?>
>>> Or do I need a TPQConnection per thread?
> Yes.
>>> I somehow failed to find any information about sqldb and
>>> multithreading:>
>>> http://www.freepascal.org/docs-html/fcl/sqldb/index.html>
>>> http://wiki.freepascal.org/SQLdb_Programming_Reference
> Because it is not thread safe. It is on my todo list.

I was going to post a pointer to previous discussion at
http://comments.gmane.org/gmane.comp.ide.lazarus.general/46205 but I
think Michael's comment here overrides anything that's been said
previously :-)

Irrespective of whether the underlying components are thread-safe, I
think it's advisable to protect them from reentry with a critical
section. There's just too much to go wrong in libpq etc., and in
particular there's a risk of mayhem if somebody migrates an app to a
database less-tolerant.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]
--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Multithreaded app with PostgreSQL connection

Graeme Geldenhuys-3
In reply to this post by Ondrej Pokorny
On 2016-06-20 17:08, Ondrej Pokorny wrote:
> I have a multithreaded application where every thread needs to connect
> to the same PostgreSQL database. How to design the DB connectivity for a
> multithreaded application correctly?

Having already written your application, it is probably too late to
switch to tiOPF. tiOPF does most of the work for you, and it takes care
of multi-threaded access to a single DB connection. It has support for
multiple DB connections, a connection pool and thread pool. If nothing
else, it might be worth you taking a look at what tiOPF have done to
accomplish this.

tiOPF is available on SourceForge.net.
http://www.tiopf.com

Regards,
  Graeme

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus-ide.org/listinfo/lazarus