SQL Master Class
In this course, we go beyond the basics to empower you with the skills, how-to knowledge, and hands-on expertise to work through and solve complex business issues with SQL. We go deep and hands-on with transactions, subqueries, inline views, summarized queries, the exists predicate and set operations.
This course is built from content created and delivered by Ian Wallington of QA.
- [Instructor] Okay, let's talk about transactions. This is really the domain of perhaps support people or primarily in applications. Now, this is the classic transaction, moving money from one account to another, or when we talk about a banking transaction. If you are carrying out a banking transaction, you do want both parts of it to either work or not work. You don't want money to disappear from one account and not appear in the other account. It should be, to use that database terminology, it should be atomic, indivisible, and the way to achieve that, because this would be two separate update statements. Update account one, set a balance and equal the balance minus whatever it is, say a hundred, and then another update statement to increase the balance of the other account by a hundred. What you would do is you would put both of these into a transaction. Now, in Oracle, you are either always in a transaction or it starts the transaction implicitly as soon as you issue an insert, update or delete statement and in Microsoft Sequel Server, my SQL, you have to specifically start a transaction. Begin trans, shorthand for that in Microsoft Sequel Server. Once you started a transaction you can issue any number of insert update and delete statements, but they are all pending until you commit them. So, you eventually save them. Now, typically you will be able to see the changes you made, but other users will not, so in Microsoft Sequel Server it's possible to change what's called the isolation level, so everyone can see the changes. So, when you commit, you literally run a commit statement, it will save the changes and make them permanent. Importantly, you make both changes in this case, however if something goes wrong suddenly, we try, maybe you managed to withdrawal money from the first account, but the second account is closed, therefore you can't update it. And what you'll need to do is go back to the beginning, either set the transaction to start here, so got to that point, so you almost go back in time and now you do what we call a rollback. Okay, so that's transactions, begin tran followed by either commit or rollback, begins or completes a transaction making permanent or undoing changes to your data.
Head of Content
Andrew is an AWS certified professional who is passionate about helping others learn how to use and gain benefit from AWS technologies. Andrew has worked for AWS and for AWS technology partners Ooyala and Adobe. His favorite Amazon leadership principle is "Customer Obsession" as everything AWS starts with the customer. Passions around work are cycling and surfing, and having a laugh about the lessons learnt trying to launch two daughters and a few start ups.