Обсуждение: tuple concurrently updated
One of my machines has two CPUs, and in some cases I build a pair of indexes in parallel to take advantage of this. (I can't seem to do an ALTER TABLE ADD PRIMARY KEY in parallel with an index build, though.) Recently, though, I received the message "ERROR: simple_heap_update: tuple concurrently updated." Can anybody tell me more about this error and its consequences? cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
I guess two transactions updated a tuple concurrently. Because versioning scheme allows old versions can be read by another transaction, the old version can be updated too. For example, We have a row whose value is 1 create table t1 (i1 integer); insert into t1 values(1); And, Tx1 executes update t1 set i1=2 where i1=1 Tx2 executes update t1 set i1=10 where i1=1 Now suppose that Tx1 read i1 with value 1 Tx2 read i1 with value 1 Tx1 updates i1 to 2 by inserting 2 into t1 according to versioning scheme. Tx2 tries to update i1 to 10 but fails because it is already updated by Tx1. Because you created different index with different transaction, The concurrently updated tuple cannot be any of index node. If the index on the same class, two concurrent CREATE INDEX command can update pg_class.relpages at the same time. I guess that is not a bug of pgsql, but a weak point of MVCC DBMS. "Curt Sampson" <cjs@cynic.net> wrote in message news:Pine.NEB.4.44.0207180848400.681-100000@angelic.cynic.net... > > One of my machines has two CPUs, and in some cases I build a pair > of indexes in parallel to take advantage of this. (I can't seem > to do an ALTER TABLE ADD PRIMARY KEY in parallel with an index > build, though.) Recently, though, I received the message "ERROR: > simple_heap_update: tuple concurrently updated." Can anybody tell > me more about this error and its consequences? > > cjs > -- > Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org > Don't you know, in this new Dark Age, we're all light. --XTC > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
A solution to this problem is not versioning catalog tables but in-place updating them. Of course anther transaction that wants to update the same row in the catalog table should wait, which leads to bad concurrency. But this problem can be solved by commiting every DDL right after its execution successfully ends. Note that catalog table updators are DDL, not DML. I think that's why Oracle commits on execution of every DDL. "Kangmo, Kim" <ilvsusie@hanafos.com> wrote in message news:ahple2$1rgh$1@news.hub.org... > I guess two transactions updated a tuple concurrently. > Because versioning scheme allows old versions can be > read by another transaction, the old version can be updated too. > > For example, > > We have a row whose value is 1 > create table t1 (i1 integer); > insert into t1 values(1); > > And, > > Tx1 executes update t1 set i1=2 where i1=1 > Tx2 executes update t1 set i1=10 where i1=1 > > Now suppose that > Tx1 read i1 with value 1 > Tx2 read i1 with value 1 > Tx1 updates i1 to 2 by inserting 2 into t1 according to versioning scheme. > Tx2 tries to update i1 to 10 but fails because it is already updated by Tx1. > > Because you created different index with different transaction, > The concurrently updated tuple cannot be any of index node. > > If the index on the same class, > two concurrent CREATE INDEX command can update pg_class.relpages > at the same time. > > I guess that is not a bug of pgsql, but a weak point of > MVCC DBMS. > > "Curt Sampson" <cjs@cynic.net> wrote in message > news:Pine.NEB.4.44.0207180848400.681-100000@angelic.cynic.net... > > > > One of my machines has two CPUs, and in some cases I build a pair > > of indexes in parallel to take advantage of this. (I can't seem > > to do an ALTER TABLE ADD PRIMARY KEY in parallel with an index > > build, though.) Recently, though, I received the message "ERROR: > > simple_heap_update: tuple concurrently updated." Can anybody tell > > me more about this error and its consequences? > > > > cjs > > -- > > Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org > > Don't you know, in this new Dark Age, we're all light. --XTC > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > >
"Kangmo, Kim" <ilvsusie@hanafos.com> writes:
> If the index on the same class,
> two concurrent CREATE INDEX command can update pg_class.relpages
> at the same time.
Or try to, anyway. The problem here is that the code that updates
system catalogs is not prepared to cope with concurrent updates
to the same tuple.
> I guess that is not a bug of pgsql, but a weak point of
> MVCC DBMS.
No, it's not a limitation of MVCC per se, it's only an implementation
shortcut for catalog updates. Fixing this across all system catalog
updates seems more trouble than it's worth. It'd be nice if the
concurrent-CREATE-INDEX case worked, though.
regards, tom lane
How do you think about my suggestion to not versioning system catalogs? p.s. It's unbelivable that I got a reply from legendary Tom Lane. :) Best, Kim. "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message news:20755.1027628748@sss.pgh.pa.us... > "Kangmo, Kim" <ilvsusie@hanafos.com> writes: > > If the index on the same class, > > two concurrent CREATE INDEX command can update pg_class.relpages > > at the same time. > > Or try to, anyway. The problem here is that the code that updates > system catalogs is not prepared to cope with concurrent updates > to the same tuple. > > > I guess that is not a bug of pgsql, but a weak point of > > MVCC DBMS. > > No, it's not a limitation of MVCC per se, it's only an implementation > shortcut for catalog updates. Fixing this across all system catalog > updates seems more trouble than it's worth. It'd be nice if the > concurrent-CREATE-INDEX case worked, though. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
"Kangmo, Kim" wrote: > > How do you think about my suggestion to not versioning system catalogs? > > p.s. It's unbelivable that I got a reply from legendary Tom Lane. :) > > Best, > Kim. I can guess what Tom's going to say, since I argued your position approx. 3 years ago. Implicitly committing transactions would not allow for rollback of DDL statements. This is a great feature that PostgreSQL has striven for that Oracle lacks. 3 years ago, it seemed to be a problem, when a table created in an aborted transaction was not being correctly cleaned up. It gets in the way of implementing an ALTER TABLE DROP COLUMN easily, since rollback of a dropped column means that the underlying data must be preserved. However, the developers have made such great progress in properly rolling back DDL statements that it would be a real shame to lose such a great feature. Additionally, it could seriously break a lot of applications out there that do not expect: INSERT INTO foo VALUES (1); CREATE TABLE bar (key integer); ROLLBACK; to fail to rollback the INSERT into foo. When the original discussion came up, there were even a few of Oracle developers that didn't know Oracle was committing their transactions behind their back. Mike Mascari mascarm@mascari.com
On Thu, 25 Jul 2002, Tom Lane wrote: > "Kangmo, Kim" <ilvsusie@hanafos.com> writes: > > If the index on the same class, > > two concurrent CREATE INDEX command can update pg_class.relpages > > at the same time. > > Or try to, anyway. The problem here is that the code that updates > system catalogs is not prepared to cope with concurrent updates > to the same tuple. I see. So the error is basically harmless, and can be rectified anyway with an ANALYZE, right? cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
Curt Sampson <cjs@cynic.net> writes:
> On Thu, 25 Jul 2002, Tom Lane wrote:
>> "Kangmo, Kim" <ilvsusie@hanafos.com> writes:
> If the index on the same class,
> two concurrent CREATE INDEX command can update pg_class.relpages
> at the same time.
>>
>> Or try to, anyway. The problem here is that the code that updates
>> system catalogs is not prepared to cope with concurrent updates
>> to the same tuple.
> I see. So the error is basically harmless, and can be rectified anyway
> with an ANALYZE, right?
Other than the fact that the second CREATE INDEX fails and rolls back,
there's no problem ;-)
I was thinking that it might help to have UpdateStats not try to update
the pg_class tuple if the correct value is already present. This will
just narrow the window for failure, not eliminate it; but it'd be a
simple change and would probably help some. Another thing to look at
is merging that routine with setRelhasindex so that a CREATE INDEX
involves only one update to the table's pg_class row, not two.
regards, tom lane
On Sun, 28 Jul 2002, Tom Lane wrote: > Other than the fact that the second CREATE INDEX fails and rolls back, > there's no problem ;-) Agh! So what, in the current version of postgres, are my options for doing parallel index builds? cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC