I have 2 stored procedures that interact with the same datatables. first executes for several hours and second one is instant. So if I run first one, and after that second one (second connection) then the second procedure will wait for the first one to end. It is harmless for my data if both can run at the same time, how to do that?
4
-
Why do you need 2 connections? Run both procedures inside a transaction from the same connection. Am I missing something?Alexandros– Alexandros2014-05-09 19:55:51 +00:00Commented May 9, 2014 at 19:55
-
it is distributed system with multiple users (using the same postgre user), and if one runs long-running procedure other users can run any other until previous one completes.BorisT– BorisT2014-05-09 21:01:28 +00:00Commented May 9, 2014 at 21:01
-
In fact they run concurrently, but the second procedure is waiting for the first one to complete. It's really hard to guess why, as we don't know what they are doing.klin– klin2014-05-09 21:08:08 +00:00Commented May 9, 2014 at 21:08
-
i know that, but how to make second one not to wait?BorisT– BorisT2014-05-09 21:34:15 +00:00Commented May 9, 2014 at 21:34
Add a comment
|
1 Answer
The fact that the shorter query is blocked while being on a second connection suggests that the longer query is getting an exclusive lock on the table during the query.
That suggests it is doing writes, as if they were both reads there shouldn't be any locking issues. PgAdmin can show what locks are active during the longer query and also if the shorter query is indeed blocked on the longer one.
If the longer query is indeed doing writes, it's possible that you may be able to reduce the lock contention -- by chunking it, for example, which could allow readers in between chunked updates/inserts -- but if it's an operation that requires an exclusive write lock, then it will block everybody until it's done.
It's also possible that you may be able to optimize the query such that it needs to be a lower-level lock that isn't exclusive, but that would all depend on the specifics of what the query is doing and your data.
