Skip site navigation (1) Skip section navigation (2)

Re: RI

From: Mladen Gogala <mgogala(at)vmsinfo(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: RI
Date: 2010-06-24 20:56:59
Message-ID: 4C23C69B.1020701@vmsinfo.com (view raw or flat)
Thread:
Lists: pgsql-novice
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




In response to

  • Re: RI at 2010-06-24 19:55:13 from Mladen Gogala

pgsql-novice by date

Next:From: MattKDate: 2010-06-24 21:35:12
Subject: Disable autostart on Mac?
Previous:From: Mladen GogalaDate: 2010-06-24 19:55:13
Subject: Re: RI

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group