[fpc-pascal] Postgresql Concurrent Transactions

[Ludo Brands]

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.

Are the tables locked or is the server just slow? What does 'select * from
pg_catalog.pg_locks where granted is false' return? For a query that lists
interdependent lock information see
<http://wiki.postgresql.org/wiki/Lock_dependency_information>
http://wiki.postgresql.org/wiki/Lock_dependency_information.


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?

That I don't understand. You started with each thread having its own
Tconnection,TTransaction, Tquery. Here you say you put a memory barrier to
stop access to Tconnection,Tquery from other threads. 
"During the problematic transaction": is this a one query transaction or do
you have a lot of queries on multiple tables in the transaction, triggers
included? What is your isolation level for these transactions?
What exactly do you want to share in transactions for TPostgres component?
How are TPostgres components related to a server locking tables?  


How have anyone who've encountered this problem solved this issue?  Any
feedback is welcome. 
 

I haven't run into this problem yet but some general advise:
1) Determine what the exact cause of the lock is before implementing
remedies.   
2) The postgresql components make a lot of connections. When serving that
many clients, you will probably benefit from connection pooling. Look at
something like pgpool-II  http://pgpool.projects.postgresql.org/.
3) Putting 200MB files in a database is a bad idea
 
Ludo
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20120622/150d6768/attachment.html>