Quiz Notes (Transactions and Locks Questions)

[abizermamnoon]

Hi Mike,

In Problem 55, after line 7, I executed this command to find row level locks:

SELECT *
FROM t, pgrowlocks('t') AS p
WHERE p.locked_row = t.ctid;

 a | locked_row | locker | multi | xids  |       modes       | pids
---+------------+--------+-------+-------+-------------------+------
 9 | (0,2)      |    706 | f     | {706} | {"For Key Share"} | {47}
(1 row)

Is this row level lock generated by table u somehow?

In Problem 64, on line 10, my hypothesis was that an error would be thrown because u = 8 is not in table t. However, it got blocked. Why did it get blocked?

[mikeizbicki]

Problem 55: table u has a FOREIGN KEY constraint on table t. That means that whenever you INSERT INTO u, you need to run a corresponding SELECT statement on t in order to figure out if the INSERT violates the constraint. This "hidden" SELECT is generating this For Key Share lock.

Problem 64:

• Line 8 performs the insertion into u. No check of table t is performed because of the DEFERRABLE INITIALLY DEFERRED line in the FOREIGN KEY constraint.
• Line 9 creates an EXCLUSIVE LOCK on table t in exclusive mode.
• On line 10, the COMMIT causes us to check the FOREIGN KEY constraint that we deferred from line 8 above. This requires running a SELECT in order to determine if the value 8 is in t. Running this SELECT blocks because of the EXCLUSIVE LOCK created on line 9.