Introduction to PostgreSQL Transaction
PostgreSQL transaction handle using the commit, begin, and rollback statements, PostgreSQL database transaction is a unit of work which consists the one or more statements. An example of a complete transaction transfers money from one bank account to another bank account or withdraw money from ATM, a complete transaction in any database is consist of debiting money from one account and successfully credit it on another account. PostgreSQL transaction is ACID (Atomicity, Consistency, Isolation, and Durability) compliant, transaction in PostgreSQL is fully ACID compliant. Transaction in any database consists of one or more statement which executes as per order.
How Transaction Works in PostgreSQL?
Below is the working of the transaction statement in PostgreSQL.
1. There are different use or work of each statement in PostgreSQL. Below is the properties of transactions.
2. Atomicity is consists of operations that we have performed on the database is fully completed or not completed. If the transactions are failed in the middle then the transaction is rollback up to the last save point.
3. Consistency properties in PostgreSQL define as the database was properly changed its state of transactions up to the last transactions savepoint. At the time of working on transactions, consistency of transactions is more important.
4. Isolation in PostgreSQL is defined as enable the transaction operations to complete and operate independently. Also, the running transaction which was active on the server is transparent to each other.
5. Durability in PostgreSQL is defined as we need to ensure that the result of operations which was we have performed on the database consists a failure. Durability is the most important property of transactions in PostgreSQL.
6. Transaction in PostgreSQL defines as the propagation of one or more changes which was we have performed on the database.
7. PostgreSQL database transaction is also defined as insert record on table, delete rows from a table or update the rows on the table.
8. A transaction is can be single updation, insertion, or deletion or it can be multiple updation, deletion or insertion statements.
9. While we have to perform the transaction on the database, it is very important to control the transactions to ensure that transaction is successfully completed or not. Also, we need to handle the error of the database which was occurred at the time of transaction running.
10. We can club the number of queries in a single set and after creating set we can execute it one by one in single transactions.
11. The transaction is very important and useful in every database, it is also the fundamental concept of the PostgreSQL database.
12. Begin, commit, rollback, and savepoint is the transaction control commands which was we have used in PostgreSQL.
13. Begin command in PostgreSQL is defined as the start of the transaction. We can start the transaction using begin keyword in PostgreSQL. We can also start the transaction using the begin transaction statements.
14. Commit command is used to save the transaction which was we have executed on the database. We have to use commit keyword to save the transaction in PostgreSQL.
15. We can also use the end transaction to commit the transaction which was executed on the database server.
16. Rollback command is used to roll back the transaction to a specific point. Rollback is a very important and useful command of transaction control in PostgreSQL.
17. Save point is defined as the partial rollback of transaction which was we have performed on the database.
18. Transaction control statement in PostgreSQL will be used with only DML (Data manipulation language) commands. DML commands is insert, update, and delete. Transaction control command is not used with the creating and dropping the database or tables.
19. Creating and dropping operations was automatically committed on database, so there is no need to commit the transaction every time.
20. After successfully committing the transaction we cannot rollback the same. To roll back the transaction we need to set the auto commit off on the database.
21. In the PostgreSQL default setting of autocommit command is ON. The below example shows that the default setting of autocommit command are as follows.
PostgreSQL Transaction Statements
Below is the transaction statements which was used in PostgreSQL.
- Begin statement is a transaction statement used to start a new transaction. To start a new transaction we have using begin statements in PostgreSQL.
- Below is the syntax of the begin statement in PostgreSQL.
1. Begin OR
2. Begin transaction OR
3. Begin work
- The above syntax is the same work while using begin transaction or begin work.
- Below is the example of a begin statement in PostgreSQL. We have inserted a statement after the beginning statement.
- Insert statement is successfully executed before we have the beginning statement.
INSERT INTO tran_test (id, first_name, last_name, phone, address) VALUES (1, 'ABC', 'PQR', 1234567890, 'Mumbai');
- Commit command in PostgreSQL is very important to save the transaction into the database server.
- Below is the syntax of the commit statement in PostgreSQL.
1. Commit OR
2. Commit transaction OR
3. Commit work
- Above syntax is the same work while using commit transactions or commit work.
- Below is the example of a commit statement in PostgreSQL. We have to insert two statements into the database after inserting we have committed the same on the database.
INSERT INTO tran_test (id, first_name, last_name, phone, address) VALUES (2, 'PQR', 'XYZ', 1234567890, 'Pune');
INSERT INTO tran_test (id, first_name, last_name, phone, address) VALUES (3, 'ABC', 'XYZ', 1234567890, 'Mumbai');
- Rollback is used to roll back the transaction from a specific point. Below is the syntax of the rollback statement in PostgreSQL.
1. Rollback OR
2. Rollback transaction OR
3. Rollback work
In the above example, we have inserted the below statement into the table and the same statement is rolledback after inserting.
INSERT INTO tran_test (id, first_name, last_name, phone, address) VALUES (101, 'AB', 'CD', 1234567890, 'Delhi');
Select * from tran_test;
This is a guide to PostgreSQL Transaction. Here we discuss the Introduction to PostgreSQL Transaction and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –