this is really bugging me. am i doing something stupid?
On Fri, Feb 8, 2008 at 2:18 PM, Willy-Bas Loos <[EMAIL PROTECTED]> wrote: > ok, that explains. > so i go on with my test, which still doesn't turn out as i expected > (PostgreSQL 8.1.10). > why?? > > ==in TTY1== (start.sql) > create table test (id int4 primary key); --expect sucess > insert into test (id) values (1); --expect success > > ==in TTY2== (tr1a.sql) > begin; --expect success > drop table test; --expect success > create table test (id int4 primary key); --expect success > insert into test (id) values (2); --expect success > > ==in TTY1== > SELECT * FROM test; --1. expect no answer now, there's an exclusive lock > on "test" from TTY2. > > ==in TTY2== > SELECT * FROM test; --2. expect 1 record, value 2. > > ==in TTY3== (tr2a.sql) > begin; --3. expect success > drop table test; --4. expect no answer now, there's an exclusive lock on > "test" from TTY2. > create table test (id int4 primary key); --5. > insert into test (id) values (3); --6. > > > ==in TTY1== > --7. expect no answer still, there's an exclusive lock on "test" from TTY2 > > ==in TTY2== > SELECT * FROM test; --8. expect 1 record, value 2 (the exclusive lock is > from here) > > ==in TTY2==(tr1b.sql) > insert into test (id) values (4); --9. expect success > commit;--10. expect success. transaction 1 (here in TTY2) will be > committed, the SELECT (in TTY1) and transaction 2 (in TTY3) can continue. > > ==in TTY1== > --11. expect result at last, value 2 only. (concurrent transaction 2 (in > TTY3) completes after this, and will delete values 2 and 4 (added after > select was issued) upon commit) > --11. true result: ERROR: relation <large nr> deleted while still in use > -- remark: I guess transaction2 was faster? This isn't right. the select > statement should only see transactions that were committed before it was > issued. > -- wait, that can't be true, transaction 2 (in TTY3) was rolled back! > > ==in TTY2== > SELECT * FROM test; --12. expect no answer now, there's an exlusive lock > on "test" from TTY3, so let it wait > --12. true result: 2 records, values 2 and 4. > -- remark: transaction 2 was rolled back, so there is now only the result > of transaction1 (in TTY2), which is, in itself, correct. > > ==in TTY3== > --message: ERROR: tuple concurrently updated > -- remark: ?? Huh? > > > -- ==END OF MY EXERCISE DUE TO ERROR CONDITION== -- > --actions as planned below-- > > SELECT * FROM test; --13. expect 1 record, value 3 (the exclusive lock is > from here) > > ==in TTY3==(tr2b.sql) > insert into test (id) values (5); --14. expect success > commit;--15. expect success > > --transaction 2 has been committed,there are no more locks, 2 values > remain: 3 and 5. > ==TTY1== > SELECT * FROM test; --16. expect 3 and 5 > ==TTY2== > SELECT * FROM test; --17. expect 3 and 5 > ==TTY3== > SELECT * FROM test; --18. expect 3 and 5 > > >
