If you need to perform multiple database operations, then in most cases you need to ensure that every operation is successful, for the sake of data integrity. A good database architecture in not always enough to solve potential integrity issues. This is the case where you should use transactions. Let's take as an example a virtual wallet that can be represented as shown in the next few tables.
The User table looks like the following:
|
ID |
NAME |
|---|---|
|
1 |
John Doe |
The Wallet table looks like this:
|
ID |
USER_ID |
BALANCE |
|---|---|---|
|
1 |
1 |
5000 |
The Wallet transactions table looks like the following:
|
ID |
WALLET_ID |
AMOUNT |
DESCRIPTION |
|---|---|---|---|
|
1 |
1 |
5000 |
Bonus credit |
|
2 |
1 |
-1800 |
Apple store |
How can we create a new user, credit their wallet, and then debit it as the result of a purchase action? This can be achieved in three ways using transactions:
Manual transactions
Implicit transactions
Isolated transactions