[fpc-pascal] Postgresql Concurrent Transactions

[Andrew Brunner]

Hi there,

I'm seeking help to address an issue I presently have with a Server
application that uses TPostgres components.

Each manager thread has its own Connection, Transaction, and TQuery.
Each manager can handle potentially thousands of sockets
Each server instance (1 process) can have up to ~100 manager threads.

Each Manager executes socket "commands" that translate at some point, into
a Database, Select, Update, or Delete.

My current problem:
If a user updates a table by uploading large a video (say about 200+MB)
 the sql server locks pretty much every table and prevents other sockets
which during the SQL execution.  This is true for Delete and Update as of
v9.1.

Implications.  Parallel commands executed on other threads aren't able to
execute b/c the postgresql server just sits there waiting for the update
transaction to complete.   PostgreSQL is locking all subsequent calls to
even other tables.

Remediation: Memory barriers are already in place to ensure that other
threads don't access the TConnection,TQuery or other things during the
problematic transaction.  Is there a way to use specify a SHARE mode in
transactions for TPostgres component?

How have anyone who've encountered this problem solved this issue?  Any
feedback is welcome.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20120621/7a6cd4f0/attachment.html>