Introduction to Database Transaction

Database Transaction

Database transaction is a collection of multiple database operations (ex: SELECT, UPDATE, DELETE, or INSERT) as a single unit of work. It represent real-world events of any company. Error handling becomes much simple for…



Database Transaction

Database transaction is a collection of multiple database operations (ex: SELECT, UPDATE, DELETE, or INSERT) as a single unit of work. It represent real-world events of any company. Error handling becomes much simple for an application when using transactions.

We can define where the transaction will begin and when the transaction will end. To start the transaction we use BEGIN syntax.

If queries in a transaction ran successfully, then we save the changes using COMMIT syntax. If queries in a transaction failed to run, then we revert the changes before the transaction ran using ROLLBACK syntax.

Now, let’s see the example of a transaction.



Example

Alice wants to transfer $100 to John. In the database, ACCOUNT table stored Alice and John account data.

Alt Text

In a transaction, the operations are:

// Select Alice account data
// Check the balance
SELECT BALANCE FROM ACCOUNT WHERE ID = 1

// Substract 100 from Alice balance
UPDATE ACCOUNT SET BALANCE = BALANCE - 100 WHERE ID = 1

// Add 100 to Alice balance
UPDATE ACCOUNT SET BALANCE = BALANCE + 100 WHERE ID = 2

If the operations was success, then we have to commit the transaction using COMMIT

Alt Text

If one of the operations was failed, then we have to rollback the transaction using ROLLBACK

Alt Text



Sources

Wangde – A Deep Dive into Database Concurrency Control

Pulkit Gupta – System Design: Database Transactions & ACID compliance (Part 1)

Adam Edelweiss – Relational Database 10 | Database Transaction

Hussein Nasser – What is a Database transaction?


Print Share Comment Cite Upload Translate
APA
Muhammad Ilham hidayat | Sciencx (2024-03-28T22:11:43+00:00) » Introduction to Database Transaction. Retrieved from https://www.scien.cx/2021/04/10/introduction-to-database-transaction/.
MLA
" » Introduction to Database Transaction." Muhammad Ilham hidayat | Sciencx - Saturday April 10, 2021, https://www.scien.cx/2021/04/10/introduction-to-database-transaction/
HARVARD
Muhammad Ilham hidayat | Sciencx Saturday April 10, 2021 » Introduction to Database Transaction., viewed 2024-03-28T22:11:43+00:00,<https://www.scien.cx/2021/04/10/introduction-to-database-transaction/>
VANCOUVER
Muhammad Ilham hidayat | Sciencx - » Introduction to Database Transaction. [Internet]. [Accessed 2024-03-28T22:11:43+00:00]. Available from: https://www.scien.cx/2021/04/10/introduction-to-database-transaction/
CHICAGO
" » Introduction to Database Transaction." Muhammad Ilham hidayat | Sciencx - Accessed 2024-03-28T22:11:43+00:00. https://www.scien.cx/2021/04/10/introduction-to-database-transaction/
IEEE
" » Introduction to Database Transaction." Muhammad Ilham hidayat | Sciencx [Online]. Available: https://www.scien.cx/2021/04/10/introduction-to-database-transaction/. [Accessed: 2024-03-28T22:11:43+00:00]
rf:citation
» Introduction to Database Transaction | Muhammad Ilham hidayat | Sciencx | https://www.scien.cx/2021/04/10/introduction-to-database-transaction/ | 2024-03-28T22:11:43+00:00
https://github.com/addpipe/simple-recorderjs-demo