Hi everyone, This question may have an obvious answer I have somehow missed, but to what extent is locking order deterministic in PostgreSQL? For example, if requests from multiple transactions arrive in some deterministic order and acquire locks, can one assume that locks will be granted in the same order if the requests are repeated at different times or on different servers?
Lock determinism is an important issue for replication algorithms that depend on database instances to behave as state machines. Here's a simple example of the behavior I'm seeking. Suppose you have transactions T1, T2, and T3 that execute as shown below. Each line represents an "increment" of time. T1, T2, T3: begin T1: update foo set value='x' where id=25; <-- Grabs row lock T2: update foo set value='y' where id=25; <-- Blocked T3: update foo set value='z' where id=25; <-- Blocked T1: update foo set value='x1' where id=25; T1: commit T2: commit T3: commit T2 and T3 are both blocked until T1 commits. At that point, is the row lock granted to T2 and T3 in some deterministic order? Or can it vary based on load, lock manager state, etc., so that sometimes you get 'y' and sometimes 'z' as the final result? If this case turns out to be deterministic, are there other cases that come to mind that would turn out to be non-deterministic? Thanks, Robert -- Robert Hodges, CTO, Continuent, Inc. Email: [EMAIL PROTECTED] Mobile: +1-510-501-3728 Skype: hodgesrm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
