Scribd
Upload
7 views4 pages

DEmo Module 9 Transaction Control1

The document provides instructions for connecting to a CentOS 7 server and starting a PostgreSQL database. It includes SQL commands for creating a table, inserting data, managing transaction isolation levels, and handling autocommit settings. Additionally, it demonstrates the behavior of transactions under different isolation levels and the implications of locks during concurrent sessions.

Uploaded by

p.yadav2279
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views4 pages

DEmo Module 9 Transaction Control1

The document provides instructions for connecting to a CentOS 7 server and starting a PostgreSQL database. It includes SQL commands for creating a table, inserting data, managing transaction isolation levels, and handling autocommit settings. Additionally, it demonstrates the behavior of transactions under different isolation levels and the implications of locks during concurrent sessions.

Uploaded by

p.yadav2279
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Connect to centos 7 and start the server

su – root
password: mysql
su - postgres
export PATH=$PATH:/usr/pgsql-14/bin
pg_ctl -D /var/lib/pgsql/14/data start

connect to the server:


psql -U postgres -d postgres

create table emp_details(e_id int,e_name text);

insert into emp_details values(111,'rupu');

insert into emp_details values(222,'mourya');

insert into emp_details values(333,'shashi');

insert into emp_details values(444,'indu');

rollback; --doesn’t work

TURNOFF/DISABLE AUTOCOMMIT
a. \echo :AUTOCOMMIT
b. \set AUTOCOMMIT off
c. \echo :AUTOCOMMIT
d. insert into emp_details values(444,'indu');
e. rollback
read committed:
session-1 session-2
\set AUTOCOMMIT on \set AUTOCOMMIT on

show transaction_isolation;
create table emp(id int);
insert into emp values(1);
insert into emp values(2);

show transaction_isolation;

begin;
insert into emp values(3);
select * from emp;
select * from emp;
#row 3 is not reflected here as it is not committed

end;

select * from emp;

repeatable read
session-1 session-2

show transaction_isolation;

show transaction_isolation;

begin;
set transaction isolation level repeatable read;
insert into emp values(3);
select * from emp;
update emp set id=333 where id=3;

select * from emp;


#update is not reflected here as it is not committed
insert into emp values(4)
select * from emp;
end;
select * from emp;
#row 4 is not reflected here even after its
committed

open other session and execute select * from emp;


serializable:
session-1 session-2

show transaction_isolation;

show transaction_isolation;

begin;
set transaction isolation level serializable;
insert into emp values(3);
select * from emp;
update emp set id=333 where id=3;

begin;
set transaction isolation level serializable;
select * from emp;
update emp set id=333 where id=3;
end;
end;

observe row is not updated. error is raised

update lock:
session-1 session-2

show transaction_isolation;
create table emp(id int);
insert into emp values(1);
insert into emp values(2);

show transaction_isolation;

begin;
update emp set id=222 where id=2;
select * from emp;
select * from emp;
update emp set id=22221 where id=2;
#observe there is a lock imposed
end;

select * from emp;

You might also like