Thread: How to make transaction delete see data from a just completedconcurrent transaction?
How to make transaction delete see data from a just completedconcurrent transaction?
From
George Woodring
Date:
We have been using the model of updating certain data in a table of
begin;
delete from foo where bar='myfoo';
insert into foo all of the correct data for myfoo;
commit;
Our thinking was that if you had two running at close to the same time, the first transaction would finish and then the second one would run making the table consistent. However this is not the case. The second transaction is not deleting anything and we are getting double the inserted data. Our guess is that the second transaction is trying to delete the data from the start of the transaction, not from when the lock on the table is released, and that data is already gone from the first transaction.
Is there a way to make the delete re-plan to see the data inserted by the first transaction when the delete is allowed to continue?
The following is what I was using as a test case;
CREATE TABLE woody ( id serial,
constraint woody_pkey primary key (id),
mynumber int4,
myname varchar
);
CREATE INDEX myname_INDEX ON woody (myname);
INSERT INTO woody (mynumber, myname) SELECT generate_series(1, 1000), 'woody';
constraint woody_pkey primary key (id),
mynumber int4,
myname varchar
);
CREATE INDEX myname_INDEX ON woody (myname);
INSERT INTO woody (mynumber, myname) SELECT generate_series(1, 1000), 'woody';
I then placed the following into a file called woody2.sql
BEGIN;
DELETE from woody WHERE myname='woody';
INSERT INTO woody (mynumber, myname) SELECT generate_series(1, 1000), 'woody';
DELETE from woody WHERE myname='woody';
INSERT INTO woody (mynumber, myname) SELECT generate_series(1, 1000), 'woody';
connection1
iss=> \i woody2.sql
BEGIN
DELETE 1000
INSERT 0 1000
BEGIN
DELETE 1000
INSERT 0 1000
connection2
iss=> \i woody2.sql
BEGIN
BEGIN
connection1
iss=> commit;
COMMIT
COMMIT
connection2
DELETE 0
INSERT 0 1000
iss=> commit;
COMMIT
iss=> select count(*) from woody where myname='woody';
count
-------
2000
(1 row)
INSERT 0 1000
iss=> commit;
COMMIT
iss=> select count(*) from woody where myname='woody';
count
-------
2000
(1 row)
Thanks,
George
iGLASS Networks
www.iglass.net
www.iglass.net
Re: How to make transaction delete see data from a just completed concurrent transaction?
From
Tom Lane
Date:
George Woodring <george.woodring@iglass.net> writes:
> We have been using the model of updating certain data in a table of
> begin;
> delete from foo where bar='myfoo';
> insert into foo all of the correct data for myfoo;
> commit;
> Our thinking was that if you had two running at close to the same time, the
> first transaction would finish and then the second one would run making the
> table consistent. However this is not the case. The second transaction is
> not deleting anything and we are getting double the inserted data. Our
> guess is that the second transaction is trying to delete the data from the
> start of the transaction, not from when the lock on the table is released,
> and that data is already gone from the first transaction.
I think a closer statement is that the "delete" doesn't see any
uncommitted rows that might be in process of insertion by another
transaction.
I think that you could handle this by using serializable transactions,
which will fail if any concurrent transaction modifies data they
depend on. You'd then need to have a retry loop for serialization
failures.
Another approach is to try to design the table such that a uniqueness or
exclusion constraint disallows having multiple sets of data for the same
key. You'd still get failures and have to be willing to retry, but the
cause of the failure would be much more narrowly defined than it is
with a serialization failure.
regards, tom lane