Please explain why does script works this way

user_11608812 0
Viewed 93

I am working in PostgreSQL, and I have just started learning about simultaneously working transactions. My problem is when I run the whole script, I get the same transaction id. The first tuple should have 1 id and second 2 should have the same id.

When I run the script in lines (one by one) I get the correct result. Why does it work this way?

DELETE FROM test;
INSERT INTO test VALUES (1, 'A');
BEGIN TRANSACTION;
INSERT INTO test VALUES (2, 'B');
INSERT INTO test VALUES (3, 'C');
COMMIT TRANSACTION;
postgresql sql database
1 Answers

That block of code is being executed in a single transaction. You haven't said how it's being executed, but if you stick it in pgadmin and run it all together, or put it in a file and call it with psql, a transaction will be automatically started and committed at the end. The BEGIN TRANSACTION line doesn't start a new transaction because a transaction is already open. That's why executing it all together will create rows with the same transaction ids.

To demonstrate, run this line by line:

BEGIN;
INSERT INTO test VALUES (1, 'A');
SELECT txid_current();
BEGIN TRANSACTION;
INSERT INTO test VALUES (2, 'B');
INSERT INTO test VALUES (3, 'C');
SELECT txid_current();
COMMIT TRANSACTION;

You'll see each SELECT returns the same id. You'll also see a message on the second BEGIN which states a transaction is already in progress.

But if you highlight (in pgadmin) just the first INSERT and execute it (without manually doing a BEGIN), that's the only line being executed in that automatic transaction. Then the rest gets executed in a second transaction, whether you execute it one line at a time or all together, because the transaction is being handled manually.

Now if you run these lines all together:

BEGIN;
INSERT INTO test VALUES (1, 'A');
COMMIT;
BEGIN;
INSERT INTO test VALUES (2, 'B');
INSERT INTO test VALUES (3, 'C');
COMMIT;

And then run this:

SELECT xmin, *
FROM test

You'll see the transaction numbers are as expected, because now the transactions are being wholly controlled manually - one created for the first statement, and the second for the other two.

user_37330700
Related