New answers tagged

0

"Read chunks, but only update a few of them"... SELECT a chunk -- separate transaction, don't care about locking, etc munch on that chunk to figure out the 'few' ids to update BEGIN; SELECT ... WHERE id IN (list of the few) FOR UPDATE; minimal other work UPDATE them; COMMIT; -- plus checks for deadlocks, etc, and restart the code. The idea ...


17

If you know how to simulate it with 2, you know how to simulate it with 3, or with N. You have to build a cyclic graph: 2 nodes: A->B->A (process A waits on B, process B waits on A) 3 nodes: A->B->C->A (process A waits on B, B waits on C, C waits on A) ... N nodes: P1->P2->...Pn->P1 To construct the Px->Py primitive (Process X waits on Process Y) use ...


1

First of all do not use database links for larger transactions. This can cause too many problems with blocking sessions. Another hint is to keep transactions in a good size (not to small, not to big). If your table is not partitioned then write a piece of code to remove just 1000 rows, commit and delete the next 1000 rows. You said something about alter ...


0

Have you tried moving the Visits update before any modifications to visitItems? That x-lock should protect the "child" rows. Doing a full locks acquired trace (and conversion to human-readable) is a lot of work but might show the sequence more clearly.


2

Your software is most likely not using the default postgres database (that is a database that is normally reserved only to store the list of databases, users, and other globals). From psql, run \l to list the available databases. Your database will be something other than postgres, template0, or template1. Try connecting to one that is not one of the above ...


1

My understanding of this is that, yes, under a transaction your SP will hold locks for the duration of the transaction. (Note there are lots of types of locks - SQL Server could row lock, table lock, page lock etc) One SP being called simultaneously (with no other activity on your DB) causing deadlocks suggests one thing to me: the SP is not locking the ...



Top 50 recent answers are included