Re: RI
От | Mladen Gogala |
---|---|
Тема | Re: RI |
Дата | |
Msg-id | 4C23C69B.1020701@vmsinfo.com обсуждение исходный текст |
Ответ на | Re: RI (Mladen Gogala <mgogala@vmsinfo.com>) |
Список | 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.gogala@vmsinfo.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
В списке pgsql-novice по дате отправления: