¡Noted: Self-taught notes. 學習筆記!
Transactions Chapter2.210 — Transaction
Relational database in particular are transactional. What does that mean? All changes to data are made through these units of work called transactions.
The advantage of bundling work in this way is that if there’s any particular point in the transaction or an update, insert, delete statement fails to operate on the database, we can wound up rolling back the entire unit of work as a whole. (Just in case that some statements succeed, some fail.)
So a transaction can be cleared by using a command .rollback() in order to clear the entire transaction out of it.
The problem is that database system can fail, and we want the database to always be in a valid state. So if it makes sense that a given unit of work of multiple statements should only succeed if the entirety of it succeeds. Bundling work into transaction will ensure that when any part of the transaction does fail, that our database is still in some sort of state that doesn’t cause application failures down the line.
Takeaways
- Databases are interacted using client-server interactions, over a network
- Postgres uses TCP/IP to be interacted with, which is connection-based
- We interact with databases like Postgres during sessions
- Sessions have transactions that commit work to the database
Transactions capture logical bundles of work.
Work is bundled into transactions, so that in case of system failures, data in your database is still kept in a valid state (by rolling back the entire transaction if any part of it fails). To ensure a database is consistent before and after work is done to it, databases uses atomic transactions, and actions like commits and rollbacks to handle failures appropriately. Transactions are, in other words, ACID.
Savepoint (no need to rollback whole transaction)
Use the SAVEPOINT statement to identify/define a point in a transaction to which you can later roll back.
Define/Establish a savepoint：SAVEPOINT savepoint_nameDelete a savepoint：RELEASE SAVEPOINT savepoint_nameroll back to savepoint：ROLLBACK TO SAVEPOINT savepoint_nameroll back to savepoint：ROLLBACK TO SAVEPOINT savepoint_name
ACID Properties
Notes/Summary
Why use Transaction -> Transaction bundles a single or multiple SQL statements as a whole to prevent scenario like bunch of users interact with database at the same time or multiple task are made to a database at the same time and some tasks succeed and some fail. That would be database disaster. You don’t even know which were done, which were not done yet, and database is in a mess already.
transaction.commit()transaction.rollback()SAVEPOINT savepoint_nameRELEASE SAVEPOINT savepoint_nameROLLBACK TO SAVEPOINT savepoint_name
Statement may a lit bit divers by different database.
Reference:
Udacity-Full Stack Web Developer
https://www.bmc.com/blogs/acid-atomic-consistent-isolated-durable/
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10001.htm
https://www.postgresql.org/docs/9.1/sql-savepoint.html
— The End —
Published on 2021/04/12 & Last edited on 2021/04/12