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