Transactions, Locks, and Deadlocks

Transactions are a way to group database operations so they can all be completed as one unit. In case an operation fails, then they all can be undone together. This is useful for ensuring the integrity of the data and reducing the risk of errors in our database.

Within transactions, we can perform any number of queries and other database operations, including updates, deletes, and inserts. When we finish our transactions, they will either be all completed or none will be completed. This provides high data consistency and helps ensure that our database remains reliable and accurate.

Commands to control transactions

PostgreSQL transactions are implemented using the SQL standard BEGIN, COMMIT, and ROLLBACK commands. We can start a transaction by issuing a BEGIN command, followed by one or more queries. We must issue a COMMIT command to commit our changes and finish the transaction. Alternatively, we can also use the ROLLBACK command to undo any changes made till the most recent BEGIN command within the transaction and cancel the transaction. A SAVEPOINT can also label a specific transaction point like a bookmark, which can be used to partially roll back the transaction to this specific point later. Now, let’s look at each of these commands in detail.

The BEGIN Command

The BEGIN command is used to start a new transaction in PostgreSQL.

The syntax for the BEGIN command is as follows:

Alternatively, we may also use the BEGIN TRANSACTION command to start a new transaction. In this course, we’ll be using BEGIN commands for conciseness.

To manually create a transaction, we can use the BEGIN command followed by the COMMIT or ROLLBACK command to complete or undo the transaction.

The COMMIT Command

The COMMIT command is used to commit the transaction and make all the changes permanent.

The syntax for the COMMIT command is as follows:

Here, we use the BEGIN command to start a new transaction. We then insert data into our ...