Concurrency control
Transactions
1. In this exercise, you will see how to rollback or commit transactions. By default PostgreSQL commits each SQL statement as soon as it is submitted. To prevent the transaction from committing immediately, you have to issue a command begin; to tell PostgreSQL to not commit immediately. You can issue any number of SQL statements after this, and then either commit; to commit the transaction, or rollback; to rollback the transaction. To see the effect, execute the following commands one at a time begin ; select * from student where name = 'Tanaka';
ID Name Dept_name Tot_Cred
--------------------------------------------------
98988 Tanaka Biology 120
delete from student where name = 'Tanaka';
Query returned successfully: one row affected, 234 ms execution time.
select * from student where name = 'Tanaka';
Total query runtime: 15 ms.
0 rows retrieved.
rollback;
NOTICE: there is no transaction in progress
Query returned successfully with no result in 16 ms.
select * from student where name = 'Tanaka';
ID Name Dept_name Tot_Cred
----------------------------------------------------------------------------
98988 Tanaka Biology 120
In the above example it explains the importance of begin and rollback used together; I have shown output after performing every command. And later have explained the observation.
Observation: In the above example we are having begin command to be executed in the first line which will indicates that until we commit the update transaction the DB system won’t save the updating in the tables. In the next line we select the details of student named ‘Tanaka’ since there is no updating it will show the record ‘Tanaka’ from the student table. The next command we perform is the delete command which removes the record of student named ‘Tanaka’ from the student table so that, the student table will be updated. In the next line we again search for the student named ‘Tanaka’ from
1. In this exercise, you will see how to rollback or commit transactions. By default PostgreSQL commits each SQL statement as soon as it is submitted. To prevent the transaction from committing immediately, you have to issue a command begin; to tell PostgreSQL to not commit immediately. You can issue any number of SQL statements after this, and then either commit; to commit the transaction, or rollback; to rollback the transaction. To see the effect, execute the following commands one at a time begin ; select * from student where name = 'Tanaka';
ID Name Dept_name Tot_Cred
--------------------------------------------------
98988 Tanaka Biology 120
delete from student where name = 'Tanaka';
Query returned successfully: one row affected, 234 ms execution time.
select * from student where name = 'Tanaka';
Total query runtime: 15 ms.
0 rows retrieved.
rollback;
NOTICE: there is no transaction in progress
Query returned successfully with no result in 16 ms.
select * from student where name = 'Tanaka';
ID Name Dept_name Tot_Cred
----------------------------------------------------------------------------
98988 Tanaka Biology 120
In the above example it explains the importance of begin and rollback used together; I have shown output after performing every command. And later have explained the observation.
Observation: In the above example we are having begin command to be executed in the first line which will indicates that until we commit the update transaction the DB system won’t save the updating in the tables. In the next line we select the details of student named ‘Tanaka’ since there is no updating it will show the record ‘Tanaka’ from the student table. The next command we perform is the delete command which removes the record of student named ‘Tanaka’ from the student table so that, the student table will be updated. In the next line we again search for the student named ‘Tanaka’ from