Transactions in PostgreSQL
Typically, a database is a shared resource so many users can access it concurrently.
When all of the users do the same transactions (CREATE, READ, UPDATE, and DELETE query) on the table, all of these things affect the state of the database that causes the conflict, to avoid this postgres uses the mechanism called transaction.
What is a Transaction?
- A transaction is a unit of instructions (Query), that is going to run in isolation, and not affect the state of the database until it is done.
- For example, running a SELECT query is reading a transaction, this is not really affecting the state of the transaction, But we consider it as a transaction because we are transacting with a database.
- But the ALTER and DELETE query would update the state of the database. So, these transactions need to be warranted.
- If both users execute the same delete query at the same time, you should ensure that nothing happens. So the concept of the transaction is to keep things consistent.
How does the transaction work?
- When multiple concurrent requests are hitting the database, and changing the same data simultaneously, the transaction must isolate requests from each other to avoid conflicts.
You may ask a question like if the two users run the same transaction at the same time, whose transaction will be executed first, this is why the transaction life cycle comes into play. Postgres first executes either one query based on various factors including the isolation level of the transaction, specific queries involved, and the time of the transaction request.
Postgres executes the transaction concurrently whenever possible.
During the database, transactions would go through multiple states, These states or called transaction states.
Active state: This is the first state during the execution of the transaction. A transaction is active as long as the instructions (Query) are executed.
Partially committed state: A change has been updated in this state. But the database has not yet committed these changes on disk. In this state, the data buffer is stored in a memory buffer, and the buffer is not yet written in the database.
Committed state: In this state, the buffer data is stored in the database. that means that the changes have been reflected in the database.
Failed state (Rollback): If the transaction is aborted (ROLLBACK) from the Active or partially committed state or the transaction is failed, the transaction is moved to the failed state.
End state: This is the last transaction state after the committed state or the failed state. This state indicates the end of the transaction.
When we run the INSERT, SELECT, UPDATE, or DELETE query, all these queries are wrapped into the transaction (The database management system already wrapping the transaction for us).
Creating own transaction
- By creating your own transaction, we can run multiple queries in a transaction. and it allows us to run multiple SQL queries at once.
-- transaction with commit
BEGIN;
-- QUERY 1;
-- QUERY 2;
-- QUERY 3;
COMMIT;
-- transaction with rollback
BEGIN;
-- QUERY 1;
-- QUERY 2;
-- QUERY 3;
ROLLBACK; -- if any of the transaciton query did unwanted thing with database, then run ROLLBACK manually- When the two users run the same transaction at the same time, postgres executes one of the transaction first (Based on rules postgres get any one of the requests to execute). Once this first transaction is committed or rollbacked, then only postgres runs the second transaction.
- If any of the queries within a transaction in PostgreSQL unintentionally modify the database in an unwanted way, you can manually execute a ROLLBACK command to undo the changes made by the transaction.
If any of the SQL queries wrapped in the transaction fail, the entire transaction will automatically fail, meaning all changes in your session will be rolled back.
The transaction can also optimize performance by grouping multiple operations into a single transaction.
Thanks for reading this article!
References
