Viewers: in countries Watching now:
In SQL Server 2008 Essential Training, Simon Allardice explores all the major features of SQL Server 2008 R2, beginning with core concepts: installing, planning, and building a first database. Explore how Transact-SQL is used to retrieve, update, and insert information, and gain insight into how to effectively administer databases. The course also covers features outside SQL Server's database engine, including technologies that have grown up around it: SQL Server Reporting Services and Integration Services. Exercise files are included with the course.
When we're updating data in our databases we'll often need to work with transactions. A transaction is an incredibly important thing in the world of databases. And to understand them the best way is to think about what we think of as a transaction in the regular world. If you talk about making a transaction, you often mean something in the world of commerce. Say you hand over $15 to the bookseller; the bookseller gives you the book. That's the transaction. It's important that both of these things happen. If you hand over the money you expect to get the book.
If they hand you the book they expect to get the money. Either both of these things happen or neither of them do. In a computing system, a classic example of a transaction is a banking system. Imagine that you log on to your bank's web site and you want to transfer $1000 from one of your accounts to the other. That's going to require to update operations. One, to subtract $1000 from account A, and the next to add $1000 to account B.
If the first part of this transaction happened and then we tried to add a thousand dollars to account B and there is a problem with it, say it was locked for editing, we need to reverse the first part of the transaction, because we never want to be in a situation where $1000 had been debited from the first account but not added to the second. This transaction needs to happen. It's a unit of work. Either both of these things happen or neither of them do. So there is a common acronym that you will come across when working with transactions in a database.
ACID, A-C-I-D. A transaction must be Atomic, Consistent, Isolated and Durable. A transaction being atomic refers to the core idea that the transaction must completely happen or not at tall. Whether there are two steps to the transaction or 20, they all contained in the transaction, they all complete successfully, or they all return to the original state, they roll back. There is no such thing as a transaction that halfway occurs.
Now whether the reason for the transaction failing is that the database had a power failure, or ran out to space to hold the new data, or there was an application error, it doesn't matter. It's an all or nothing rule. For a transaction to be consistent, it means that any transaction must take the database from one valid state to another valid state based on the rules of your database. So even if a transaction is successfully atomic it cannot result in a situation that violates any of the integrity rules defined in the database.
A transaction being isolated refers to the idea that the data in that transaction is essentially locked for the moment in which the transaction is occurring. So while we're trying to change a balance say on one of our account records, another part of the system must not be allowed to access that data until the first transaction has finished. And for a transaction to be durable, it means the transaction must be considered robust. If it happens and the database management system says this transaction is happened successfully then that transaction is guaranteed.
Say if you go to a travel website and purchase a fly on an aircraft and you've been guaranteed seat 2A, you should be able to regard that transaction as being durable. Even if half a second later that database itself had a power failure and shut down, when it's rebooted the transaction will have survived that failure. They're not going to go and sell your seat to someone else, simply because there was a glitch in the system half a second after you made your purchase. Now the great thing about working with the database management system like SQL Server is that these capabilities are built into the system.
When you're writing code there has to be a transaction, you don't have to worry about how to program these. You just need to know when to tell the database system, "now this is a transaction, that these two things, these three things, these four things must be done together," and that's the little code that you add. Bear in mind, not everything you do as a transaction, you need to decide what your transactions are and then you will be adding just a little code to control them.
Find answers to the most frequently asked questions about SQL Server 2008 Essential Training .
Here are the FAQs that matched your search "" :
Sorry, there are no matches for your search "" —to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.