Welcome to ACID transactions. After watching this video, you will be able to: Explain what an ACID transaction is Give an example of an ACID transaction Describe commits and rollbacks A transaction is an indivisible unit of work. It can consist of one or more SQL statements, but to be considered successful, either all of those SQL statements must complete successfully, leaving the database in a new stable state, or none must complete, leaving the database as it was before the transaction began. For example, if you make a purchase using your bank card, many things must happen: The product must be added to your cart Your payment must be processed - Your account must be debited the correct amount and the store's account credited The inventory for that product must be reduced by the number purchased Let's look at the example in more detail. If Rose buys boots for $200, then you can use an UPDATE statement to decrease her account balance. And another UDATE statement to add $200 to the Shoe Shop balance. And a final update statement to decrease the stock level of boots at the Shoe Shop by 1. If any of these UPDATE statements fail, the whole transaction should fail, to keep the data in a consistent state. The types of transaction in the example are called ACID transactions. Atomic - All changes must be performed successfully or not at all. Consistent - Data must be in a consistent state before and after the transaction. Isolated - No other process can change the data while the transaction is running. Durable - The changes made by the transaction must persist. To start an ACID transaction, use the command BEGIN. In db2 on Cloud, this command is implicit. Any commands you issue after that are part of the transaction, until you issue either COMMIT, or ROLLBACK. If all the commands complete successfully, issue a commit command to save everything in the database to a consistent, stable state. If any of the commands fail; perhaps Rose’s account doesn’t have enough money to make the payment, you can issue a rollback command to undo all the changes and leave the database in its previously consistent stable state. SQL statements can be called from languages like Java, C, R, and Python. This requires the use of database-specific access APIs such as Java Database Connectivity (JDBC) for Java or a specific database connector like ibm_db for Python. Most languages use the EXEC SQL commands to initiate a SQL command, including COMMIT and ROLLBACK, as you can see in this example. Remember that BEGIN is implicit, you do not need to call it out explicitly. Incorporating SQL commands into your application code gives you the opportunity to create error-checking routines that in turn control whether the transaction is committed or rolled back. In this video, you learned that: A transaction represents a complete unit of work, which can be one or more SQL statements. An ACID transaction is one where all the SQL statements must complete successfully or none at all. This ensures the database is always in a consistent state. ACID stands for Atomic, Consistent, Isolated, Durable. SQL commands BEGIN, COMMIT, and ROLLBACK are used to manage ACID transactions. SQL commands can be called from languages like C, R and Python.