headers
Andrew Brunner | 21 Jun 2012 22:49
Picon

Postgresql Concurrent Transactions

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.
_______________________________________________
fpc-pascal maillist  -  fpc-pascal@...
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Permalink | Reply |
headers
Ludo Brands | 22 Jun 2012 10:34
Picon
Favicon

RE : Postgresql Concurrent Transactions

 
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.
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
_______________________________________________
fpc-pascal maillist  -  fpc-pascal@...
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Permalink | Reply |

Gmane