#293 A problem in prepared transactions

1.0 Dev Q
closed
5
2012-05-28
2012-04-15
Abbas Butt
No

Follow these steps

1. Create two tables and insert some rows in them

create table tab1(val int, val2 int, val3 int);
insert into tab1 values(1,11,1122),(2,11,3344);
create table tab2(val int, val2 int, val3 int);
insert into tab2 values(1,11,1122),(2,11,3344);

2. Prepare a transaction

begin;
lock table tab1 in ACCESS EXCLUSIVE mode nowait;
prepare transaction 'pt1';

3. Now prepare another transaction

begin;
declare c1 cursor for select * from tab2;
fetch 1 from c1;
prepare transaction 'pt2';

4. Now run this query
select * from tab2;

Data nodes will complain
WARNING: there is already a transaction in progress

and the reason is that the prepare transaction 'pt2' command in step 3 never reaches data nodes
where as the prepare transaction 'pt1' command in step 2 does.

Lets verify

5. select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+-----+-------------------------------------+-------+----------
915 | pt1 | Sat Apr 14 04:41:13.352926 2012 PDT | edb | test
919 | pt2 | Sat Apr 14 04:41:43.294365 2012 PDT | edb | test
(2 rows)

6. execute direct on data_node_1 'select * from pg_prepared_xacts';
transaction | gid | prepared | owner | database
-------------+-----+-------------------------------------+-------+----------
915 | pt1 | Sat Apr 14 04:41:13.351781 2012 PDT | edb | test
(1 row)

7. execute direct on data_node_2 'select * from pg_prepared_xacts';
transaction | gid | prepared | owner | database
-------------+-----+-------------------------------------+-------+----------
915 | pt1 | Sat Apr 14 04:41:13.351359 2012 PDT | edb | test
(1 row)

The impact of this problem is that if we have a select for update in a transaction, and we prepare it, the table in question does not get locked on the data nodes.

Discussion

  • This behavior of prepared transaction is completely normal because at step 3 we have no data to prepare on datanodes. a 2PC occurs on datanodes where a write operations has been involved, so as here pt2 only fetches some data, there are no writes involved on remote datanodes, so no 2PC. I checked this test case, and I am not seeing Datanodes complaining about a transaction in progress at step 4.

     
  • Abbas Butt
    Abbas Butt
    2012-04-16

    We have to count SELECT FOR UPDATE/SHARE as a write operation.

     
  • Amit Khandekar
    Amit Khandekar
    2012-04-19

    • assigned_to: nobody --> amitdkhan
     
  • Abbas Butt
    Abbas Butt
    2012-05-28

    Fixed by commit ID aca5a2b063acc39366d187899ed3a5bdc0095e0d & a3842e1f11615ca2bca9a841fe5f0fb161e93776

     
  • Abbas Butt
    Abbas Butt
    2012-05-28

    • status: open --> closed