Sign in Join

Live chat for Free Pascal, Lazarus, Delphi and every Pascal programmer.

Mailing lists / lazarus / 2016-06

Multithreaded app with PostgreSQL connection

4 messages · 4 people ·

Reply by email Subscribe to this list Your subscription

Read-only archive. Use j and k to move between messages.

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

Reply by email

Michael Van Canneyt
to Ondrej Pokorny
Ondrej Pokorny wrote 5 lines
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.

Show quoted text 5 lines
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.

Reply by email

Mark Morgan Lloyd
to Michael Van Canneyt
Michael Van Canneyt wrote 20 lines
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]

Reply by email

Graeme Geldenhuys
to Ondrej Pokorny: Hello! I have a multithreaded application where every thread needs to connect to the same…
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

Reply by email