Join Bill Weinman for an in-depth discussion in this video Using transactions, part of SQL Essential Training.
The syntax for using transactions in SQL is pretty simple. In this lesson, I'll demonstrate two common uses for transactions. First I'll show you how to maintain the integrity of your database by ensuring that related statements are completed successfully, before they're committed to storage. Going to be using the test.db database here, for this lesson. And to start with, I'm going to copy and paste some SQL from the exercise files for Chapter 8. I'm going to create a couple of tables, and insert some data into them to start with.
So I'm pressing Cmd+C on my Mac, you use Ctrl+C on a PC, and then I paste it into SID, into the SQL box using Cmd+V or Ctrl+V and there's the tables and you see we've created a inventory table and a Sales table. And then, we've inserted some rows into the Inventory table. And select asterisk from widget inventory: and there's the result. Now, I'm just going to select all of this, and delete it.
And, we'll go back to the exercise file here, and, I'm going to grab this part from begin transaction to the select from inventory and sales, and I'll copy and paste that. And here we have a transaction which inserts into sales and updates the widget inventory. So I'm going to go ahead and press go, and you see that we've sold 5 items and we've reduced the on hand in the inventory by 5, and this is done with these statements here.
We have INSERT INTO widgetSales and there's our sales data down there in the Sales table and we've updated the widget inventory, reducing the on hand by the amount of our sales, 5, where ID equals 1, cause we happen to know. Obviously, in practice, these would be more complex statements. But just for demonstration purposes, I wanted to show you. I have this wrapped in a transaction. I begin transaction before it and end transaction after it. The begin transaction statement tells the system that all the statements up to the end transaction statement, are to be treated as one unit.
And the end transaction statement, ends that transaction and allows the system to commit those statements in that transaction to storage. Keep in mind that the syntax may be different on different systems, begin transaction may be start transaction or just begin on some systems. End transaction maybe commit transaction or just commit or end on some systems. Many systems also allow different syntax as equivalence. So that's fundamentally how you use transactions.
If for some reason you find that you need to roll back a transaction before completing all of the steps, you can use the rollback statement. For example, inside of this transaction, I may insert something into the widget inventory, and so let's say that my program starts the transaction and then performs that insert. And then realizes, for some reason, that it needs to roll it back. For that, you use the Rollback statement. And, you'll notice that when I execute this, this extra row with toy in it does not show up in our widget inventory table.
And there's no error, it's just been rolled back, and that ends the transaction without committing the data to storage. So now I'm going to restore this database to its original state, by dropping both of these tables that we created. And I'd like to demonstrate another use for transactions. Transactions are also commonly used to increase performance. If you have a long list of inserts, or updates, that are performed as a unit, they can be performed much faster. For this purpose, we're going to create a table.
I'm going to go back here and do some more cut and paste here. This is the table that we'll create. It's very simple. Now the table is created. And then I'm going to copy and paste this insert statement a thousand times. So, 1, 2, 3, 4, 5. And I'll take those 5 and we'll copy and paste them twice, that makes 10. And now if I copy and paste the 10, 10 times, I'll get 100. 6,7,8,9,10. And do that 10 times and I'll get 1000.
1, 2, 3, 4, 5, 6, 7 ,8, 9, 10. I now have 1000 inserts in my little text box here. And when I press Go, you'll notice this takes a little while. You see here in our status it took 6700 milliseconds, that's 6.7 seconds. So that's a good amount of time for 1,000 inserts. But here's the beautiful thing. If I come back up here to the top, and I say BEGIN TRANSACTION, and then I scroll all the way down here to the bottom, and I say end transaction. And I'm just going to select count from the table, we should end up with 2000 rows.
I'm going to press go. And you see this happened in 29 milliseconds. That's much, much faster. We now have 2000 of 'em. If I do it again, see it took 27 milliseconds and we now have 3000 of 'em. So, transactions can really increase the performance when you have a whole lot of data inserts, because the system doesn't have to confirm for each insert that the data has been written safely. So I'm going to go ahead and drop this test table. To bring our database back to its original state.
Transactions are a valuable database tool and using them in SQL is very simple. Keep in mind that the exact names of the statements do differ on different systems so you'll need to consult your documentation to find the exact syntax for your system.
- Understanding SQL terminology and syntax
- Creating new tables and records
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Finding the numeric type of a value
- Using aggregate functions and transactions
- Updating a table with triggers
- Creating views
Skill Level Beginner
Q: For Mac OS X: When I try to start the Apache Web Server from the XAMPP control panel, it doesn't start, and when I open "localhost" in my web browser, I see a white screen that says "It Works!" instead of the XAMPP page.
sudo apachectl stop
Q: I'm on a Mac, and I get an error in SID that says "attempt to write a read only database." How can I fix this?
A: This usually means that the database folder does not have sufficient permissions for writing by the web user. This can happen if you create the SQL folder new, rather than copying it from the Exercise Files. Here's how to fix this:
- Open a Finder window and Navigate to /Applications/XAMPP/htdocs/SQL
- Control-click on the SQL folder and select "Get Info" from the context menu.
- Under "Sharing and Permissions" (you may need to open the disclosure triangle), in the "everyone" row, select "Read & Write."Then you can close the Info window.
- Now repeat the process for the three *.db files inside the folder.