Database Transaction 📊

A Database transaction is one or more database operations executed as a single unit of work, which ensures that changes to the data occur completely or not at all. It is important in database systems to ensure data consistency and integrity. In transactions, there are four main properties that are often referred to as ACID:

1. Atomicity 🔒: guarantees that all operations in a transaction are fully executed or none are executed at all. In case of failure of any of the operations, then the entire transaction will be canceled.
2. Consistency 📏: ensuring that the processed data meets all the rules and restrictions that have been defined in the database.
3. Isolation 🛡️: provides isolation so that transactions running simultaneously do not affect each other.
4. Durability 💾: guarantees that changes made by completed transactions will remain stored even in the event of a system outage.

Examples Of Database Transaction Use Cases 💡

Transactions are very important in the financial system, for example, when a user transfers funds between accounts. The operation includes two important steps:

• Reduce the sender's account balance 💸.
• Add to the recipient's account balance 💰.

If only one operation is successful, then the balance in one of the accounts will not match. Using transactions, these two operations are executed as a single unit of work. If one of the operations fails, the entire transaction will be canceled 🔄.

Steps in Database Transaction 📝

Here are the general steps in running a database transaction:

1. Begin Transaction 🏁: start a transaction.
2. Execute database operations 🔄: such as
   ,
   , or
   .
3. Commit ✅: stores changes permanently in the database after all operations in the transaction have been successfully executed.
4. Rollback ⏪: restores the database to its original state in the event of a failure in one of the operations, so that no changes are saved.

Isolation Level Type 🔐

The Isolation level determines the degree to which a transaction should be isolated from other transactions. Here are some commonly used types of isolation levels:

1. Read Uncommitted 👀: allows a transaction to read changes that have not been saved (not yet committed) by another transaction.
2. Read Committed 📖: a transaction can only read data that has been committed by another transaction.
3. Repeatable Read 🔄: prevents another transaction from changing the data being read by the current transaction until the transaction is completed.
4. Serializable 🔒: provides the highest level of isolation by ensuring that transactions run as if in sequence one at a time, without any transactions running concurrently.

Database Transaction Implementation 🖥️

Examples of transaction implementation in SQL:

In the code above, the first UPDATE operation reduces the balance of Account 1 by 100, then the second UPDATE adds the balance of Account 2 by 100. If one of the operations fails, we can perform a ROLLBACK to restore the database to its original state 🔄.