Database
We will cover all topics about database engineering from ACID properties to indexing, partitioning, sharding, replication, security etc .
Transactions
What are transactions in relational databases?
It’s a collection of db querries which are treated as one unit of work.
Transaction always begins with the keyword BEGIN.
Lifespan of a transaction:
- Transaction BEGIN
- Transaction COMMIT
- Transaction ROLLBACK
- Transaction unexpected ending = ROLLBACK (eg: crash)
Transactions are used to change and modify data, however it is perfectly fine to have readonly transaction.
| ACCOUNT_ID | BALANCE |
|---|---|
| 1 | $1000 |
| 2 | $500 |
Ex: send 100$ from account 1 to account 2.
- BEGIN TX1
- SELECT BALANACE FROM ACCOUNT WHERE ID = 1 (BALANCE is $1000)
- BALANCE > 100
- UPDATE ACCOUNT SET BALANCE = BALANCE - 100 WHERE ID = 1 (now BALANCE for ID 1 is 900)
- UPDATE ACCOUNT SET BALANCE = BALANCE + 100 WHERE ID = 2 (now BALANCE for ID 2 is $600)
- COMMIT TX1
NOTE: A transaction is allways started, when you don’t start a transaction then it already starts one on the backend. Be it an INSERT OR UPDATE statement, on the backend the database starts a transaction implicitely and commits it immediately. We are always in a transaction, some are user defind and some are built and implicitely defind by the system.
Atomicity
Everything must be treated as one unit. All queries in a transaction must succeed.
If one query fails, all prior successful querries in the transaction should rollback.
If the database went down prior to a commit of a transaction, all the successful querries in the transactions should rollback (once the db starts).
In some databases before the COMMIT any writes you make in the query are writing it to disks.