Re: RI

BTW, why was the transactionid column NULL? I did have "BEGIN
TRANSACTION", so I was doing things within a legitimate transaction, it
should have been non-null value.

Mladen Gogala wrote:
> I did decide to put your words to the test, so I added a foreign key to
> the well known SCOTT/TIGER schema in Postgres:
>
> scott=# \d+ emp
> Table "public.emp"
> Column | Type | Modifiers | Storage |
> Description
> ----------+-----------------------------+-----------+----------+-------------
> empno | smallint | not null | plain |
> ename | character varying(10) | not null | extended |
> job | character varying(9) | | extended |
> mgr | smallint | | plain |
> hiredate | timestamp without time zone | | plain |
> sal | double precision | | plain |
> comm | double precision | | plain |
> deptno | smallint | | plain |
> Indexes:
> "emp_pkey" PRIMARY KEY, btree (empno)
> "emp_ename_id" btree (ename)
> "ind_emp_deptno" btree (deptno)
> Foreign-key constraints:
> "fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)
> Has OIDs: no
>
> The next thing to do was to update the parent table:
> scott=# begin transaction;
> BEGIN
> Time: 0.133 ms
> scott=# update dept set dname='ACCOUNTING' where deptno=10;
> UPDATE 1
> Time: 44.408 ms
> scott=# update dept set deptno=10 where dname='ACCOUNTING';
> UPDATE 1
> Time: 0.823 ms
> scott=#
>
> The query to monitor locks was the following:
> select
> pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid,
> pg_locks.mode, pg_locks.granted,
>
> pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,30),
> pg_stat_activity.query_start,
> age(now(),pg_stat_activity.query_start) as "age",
> pg_stat_activity.procpid
> from pg_stat_activity,pg_locks left outer join pg_class on
> (pg_locks.relation = pg_class.oid)
> where pg_locks.pid=pg_stat_activity.procpid and
> pg_class.relname not like 'pg_%'
> order by query_start;
>
> The result was somewhat surprising:
>
> datname | relname | transactionid | mode | granted |
> usename | substr | query_start |
> age | procpid
> ---------+-----------+---------------+------------------+---------+---------+-----------------------+-------------------------------+----------------+---------
> scott | dept | | RowExclusiveLock | t |
> mgogala | <IDLE> in transaction | 2010-06-24 15:33:53.699563-04 |
> 00:02:41.84465 | 30861
> scott | dept_pkey | | RowExclusiveLock | t |
> mgogala | <IDLE> in transaction | 2010-06-24 15:33:53.699563-04 |
> 00:02:41.84465 | 30861
> (2 rows)
>
> There were 2 Row-X locks, one on the table, another one on the index. I
> also checked for Oracle and the locking of the child table was eliminated.
>
>
> Tom Lane wrote:
>
>> Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com> writes:
>>
>>
>>> Interesting question. When modifying the parent record, Oracle RDBMS
>>> locks the entire child table in shared mode, unless an index on the
>>> child table is present. What does Postgres do in that situation? Can
>>> Postgres somehow locate the corresponding child record(s) without an
>>> index?
>>>
>>>
>> Sure ... it'll just seqscan the child table. Obviously, this will be
>> horridly slow --- but as stated, if it's something you very rarely do,
>> you might not want to pay the overhead of an extra index on the child
>> table in order to make it faster. It's a tradeoff, you pays your money
>> and you takes your choice.
>>
>>
>>
>>> This feature of Oracle RDBMS was a source of countless deadlocks
>>> during my 20+ years as an Oracle professional. When I come to think of
>>> it, Postgres probably does the same thing to prevent an update of the
>>> child table while the update of the parent table is going on. I confess
>>> not having time to try. Can you elaborate a bit on that?
>>>
>>>
>> No, we don't lock the whole table. The way the anti-race-condition
>> interlock works is that an insert into the child table attempts to
>> share-lock the referenced (parent) row. If successful, that prevents a
>> delete of the referenced row until the child insert has committed.
>> (After it's committed, no lock is needed because any attempted delete of
>> the parent row will be able to see that there's a child row.) You can
>> get some deadlocks that way too, of course, but they're different from
>> what you're saying Oracle does.
>>
>> regards, tom lane
>>
>>
>
>
>

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions