Then, the FOR UPDATE will may avoid the deadlock problems but it is going to block a huge number of key values, which will make all the child table transactions to go into the waiting state. Now, imagine that we are using FOR UPDATE in a view or in a humdinger SQL query to avoid a deadlock. This is what was happening in the plain FOR UPDATE situation, where the child table transaction has gone into a waiting state. This is required to maintain the data consistency across the parent and child tables. Where we need to hold the lock on the key value, which also requires a lock to be held on all of it's referenced column values.
Some use cases are like updating the employee ID/SSN/Passport number, In some cases, we may really require to hold a lock on the key columns. SESSION 1 SESSION 2Īs you see, FOR NO KEY UPDATE is also solving the dead lock problem. Now, let us try whether the FOR NO KEY UPDATE solves the deadlock issue as well. Postgres=*# SELECT * FROM parent WHERE id=10 FOR NO KEY UPDATE įrom the above results, the INSERT operation in the SESSION 2 was much smooth and has completed it's activity without any waits. Now, let try the same example with the NO KEY option. To achieve this with SELECT FOR UPDATE in PostgreSQL, we could use the NO KEY option in the FOR UPDATE clause. While updating the non key column values, we could force the behavior to not hold any lock on the key columns. Rather we update only the non key columns like salary, phone number, address, etc. In majority of the cases, we actually do not need to update any key columns, In this case, the SESSION 2 transaction will wait until the SESSION 1 transaction is complete. Try to acquire the SHARE lock on the parent table's id column. In the above example we are actually updating the non key column( balance), by acquiring the lock on id column.Īs we hold the lock on primary key column id, when we try to insert any value into the child table, then it will Now, let us discuss about the FOR UPDATE behavior in PostgreSQL.īy default, when we use FOR UPDATE, it means that it is going to return the rows which are retrieved by the select statement by acquiring the lock on key columns. The reason for this waiting state is due to the PostgreSQL's FOREIGN KEY data validation process between the child and parent tables. Postgres=*# INSERT INTO child VALUES(10, now()) Īs you see in the above output, the insert operation on the child table is in waiting state.Įven though the FOR UPDATE clause does not exist in the SESSION 2, the transaction would still remain in a waiting state. Postgres=*# UPDATE parent SET balance=balance-2 WHERE id=10
Postgres=*# SELECT * FROM parent WHERE id=10 FOR UPDATE BEGIN įollowing is the behavior observed when we run these two transactions concurrently. Now, consider that I have an another concurrent transaction which tries to insert the data into the child table. This is because, we have serialized the data access by using the FOR UPDATE clause. If we are just going to run the above statements concurrently, then there should not be any deadlocks among the transactions. UPDATE parent SET balance=balance-2 WHERE id=10 SELECT * FROM parent WHERE id=10 FOR UPDATE The following is an example transaction on the parent table which is using the FOR UPDATE clause to avoid any deadlock problems for this specific transaction.
CREATE TABLE parent(id INT PRIMARY KEY, balance INT) ĬREATE TABLE child(id INT REFERENCES parent(id) ON DELETE CASCADE, trx_timestamp TIMESTAMP) To start with a simple scenario, let us consider the following example with two tables and let us assume that an application is running concurrent update operations on the parent table.
0 kommentar(er)
