lock_timeout_test.sql

-- Test for lock timeout
drop table if exists pxtest2;
create table pxtest1(f int);

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
drop table pxtest1;
PREPARE TRANSACTION 'regress-one';

set lock_timeout to 60000;

-- waits upto  60sec.
select * from pxtest1;
reset lock_timeout;

Rollback prepared 'regress-one';


-- Test for cumulative lock timeout.
-- Client -1 
create table pxtest1(f int);
create table pxtest2(f int);

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
drop table pxtest1;
PREPARE TRANSACTION 'regress-one';

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
drop table pxtest2;
PREPARE TRANSACTION 'regress-two';

-- Client -2
set lock_timeout to 60000;

-- Waits for 60sec on pxtest1 lock.
select * from pxtest2, pxtest1;
reset lock_timeout;

-- Client -1
-- execute the following command after 50 sec wait of select in client - 2.
rollback prepared 'regress-two';
rollback prepared 'regress-one';


-- Executed all combinations with above test.



-- Test for cummulative when lock timeout option as lock_statement
-- Client -1
create table pxtest1(f int);
create table pxtest2(f int);

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
drop table pxtest1;
PREPARE TRANSACTION 'regress-one';

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
drop table pxtest2;
PREPARE TRANSACTION 'regress-two';

-- Client -2
set lock_timeout to 60000;
set lock_timeout_option to 'lock_statement';
select * from pxtest2, pxtest1;
reset lock_timeout;


-- Client -1
-- execute the following command after 50 sec wait of select in client - 2.
rollback prepared 'regress-two';
rollback prepared 'regress-one';



-- Test for lock timeout on row level locks.
drop table if exists pxtest1;
drop table if exists pxtest2;

create table pxtest1(f int);
create table pxtest2(f int);

insert into pxtest1 values(generate_series(1,10));
insert into pxtest2 values(generate_series(1,10));

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
delete from pxtest1 where f < 2;
PREPARE TRANSACTION 'regress-one';

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
delete from pxtest2 where f > 8;
PREPARE TRANSACTION 'regress-two';

set lock_timeout to 60000;

-- Waits for 60sec 
delete from pxtest1 where f < 2;

-- Waits for 60sec
delete from pxtest2 where f > 8;
reset lock_timeout;

rollback prepared 'regress-two';
rollback prepared 'regress-one';