Thomas Swan <[EMAIL PROTECTED]> writes:If you sort the locks before acquiring them, then you could prevent a deadlock conditions. Proper planning from the programmer can help to alleviate most of the rest.
When a SELECT FOR UPDATE query is executed, are the row level locks on a table acquired in any specific order
Nope, just whatever order the chosen plan happens to visit the tuples in.
I had remembered several readings on ordered locking as a method to prevent deadlocks, and associated that with select for update methodology. In theory if you aquired locks in the following order, for each table/relation (in oid order) get rows/tuples (in oid order), you could help avoid deadlock by never gaining a lock ahead of someone else.
Hmmm .... this would only help for situations where all the locks of interest are grabbed in a single scan. I suppose that has some usefulness, but it can hardly be said to eliminate deadlocks. I kinda doubt it's worth the trouble.
Hypothetically, if I knew the relations that contained the tuples I was locking, I could conceivably visit them in, let's say, alphabetical order or oid order or any predictable order, and then select the rows for update (using oid order or primary key order). It would be hard to induce a deadlock condition if the locks being acquired where in a consistently reproducible order by all processes.
Perhaps it's just an academic discussion, but I think it could work in terms of performance. I'm just not sure how much work is necessary to sort the locks prior to acquiring them.
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
