![]() ![]() Let's see if postgres has the same issue with unindexed foreign keys. ![]() Here is a recording of some demos and explanations I did a few years ago: Oracle Midlands #13: All About Table Locks - Franck Pachot and this is still relevant in 18c. The goal of this post is not to detail the Oracle behaviour as it is well known. insert into emp(empno,deptno) values (8001,40) update dept set deptno=41 where deptno=40 ġ. insert into emp(empno,deptno) values (8001,10) Ģ. Any delete on DEPT (or update of DEPTNO) is blocked by any insert into EMP.ġ. The worst happens when this lock takes a long time to be acquired because Oracle locks are enqueues and then any other DML on the child table has to wait behind it. Here is a list of all the combinations. Oracle has to lock the whole child table in Share more, waiting on 'enq: TM' mode Share, when the foreign key is not indexed, because an insert can go anywhere in the table. This occurs even before the rows are read, and then even if the delete does not touch any row (such as my 'delete from dept where deptno=0' here). Actually, as long as there is any DML on the child table, an intention to delete a row from the parent table, or update the key, is blocked. The second situation is more problematic because the whole child table is locked, and this happens even when the two sessions touch to unrelated rows. ![]() insert into emp(empno,deptno) values (8001,50) insert into emp(empno,deptno) values (8001,40) Ģ. update dept set deptno=41 where deptno=40 Ģ. In summary, a row lock serializes the transactions in the following situations:ġ. In this situation, the session waits for the another transaction with the wait event 'enq: TX' in mode 4 (Share). If not, ORA-02291 'parent key not found' is raised. If commited, the insert of the child is possible. The insert sees the parent row but must wait to know if this row will be commited or not. Two combinations resulted in transaction locks (TX): when inserting a child for a department that is being created by a concurrent session. However, this ASH approch makes the test very easy: just run all combinations and look at the blocking situations later. The name of the wait event mentions the lock type but the lock mode must be queried from pg_locks. For Oracle, I've queried V$ACTIVE_SESSION_HISTORY at the end, which shows the blocking session and the blocked session with its wait event detailing the lock (enqueue wait event with p1 containing the type and mode, and p2 containing the object if in the case of 'enq: TM').įor PostgreSQL, I used pg_active_session_history from the pgSentinel extension ( which provides the blocking session as well and the wait event). The first one waits 10 seconds before doing a rollback, so that we can see 10 seconds of wait if there is a blocking lock. I've run all combination, and for each, started with one or the other session. "update dept set loc='x' where deptno=40 " "update dept set deptno=41 where deptno=40 " Or it updates one DEPT with and without touching the referenced column: ![]() Or this second session inserts a new DEPTNO 50: "insert into emp(empno,deptno) values (8001,50) "Īnother session running some delete statements on a specific DEPTNO, or all of them: "insert into emp(empno,deptno) values (8001,40) " "insert into emp(empno,deptno) values (8001,10) " One session inserting a row in EMP for DEPTNO 10 (one that already has childen in EMP), 40 (with no row in EMP) or 50 (not existing in the initial state) Then I've run the combination of following statements: I have created the EMP / DEPT tables from the Oracle demo schema, with a foreign key in EMP referencing DEPT, and no index on it. PostgreSQL has a similar way to manage isolation, with MVCC, then do you think you also need to index the foreign keys? Here is a test that confirms that postgres does need to not lock the tables even without index on the foreign key. In Oracle we need to have a index on the foreign key column as soon as we have the intention to delete from the parent row, or a locking situation may block all transactions around the child table. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |