Transcript Lecture 7 PowerPoint

Instructor: Craig Duckett
Lecture 07: Tuesday, April 18th , 2017
Conflicts and Isolation, MySQL Workbench
1
• MID-TERM EXAM is LECTURE 10, Thursday, April 27th
• Assignment 2 is due LECTURE 12, Tuesday, May 9th
in StudentTracker by MIDNIGHT
• Assignment 3 is due LECTURE 20, Tuesday, June 6th
• Database Presentation is due LECTURE 20, Tuesday, June 6th
• Final Exam is LECTURE 21, Thursday, June 8th
2
3 x 150 Points (450 points Total)
• Assignment 1 GRADED!
• Assignment 2 (Stage 2): DUE LECTURE 12 Tuesday, May 9th
• Assignment 3 (Stage 3): DUE LECTURE 20 Tuesday, June 6th
• Database Presentation: DUE LECTURE 20 Tuesday, June 6th
3
Tuesday (LECTURE 7)
• Database Design for Mere Mortals: Chapter 4
4
XAMPP LITE: http://www.usbwebserver.net
5
• Conflicts and Isolation
6
Conflicts and Isolation
http://dev.mysql.com/doc/refman/5.6/en/commit.html
7
Early we had talked about transactions and the ACID test, and now it's time to talk a bit more
about them.
Earlier I had talked about the characteristics of a transaction, but here I'm going to focus just on
one part of ACID, the idea of isolation. The idea that when simultaneous users are
interacting with the database at the same time, how do we stop them from treading on each
other's toes? This is the inherent problem of concurrency, or concurrent users.
Conflicts and Isolation
Well, first, let's understand how they might conflict with each other. Here's an example.
We own a small bank, and we've got Alice and Bob who are two customers in our system and
they have three accounts: a joint account, and two separate accounts for personal
incidentals. These are each a different row in this account table, about as simple as I could
possibly make it here.
Every so often, Alice and Bob want to transfer money from their joint account into the
individual one. Every time they do, the process goes something like this (I'm going to assume
that Alice is doing this transaction):
Conflicts and Isolation
I'm just going write pseudo
the
code
here, because it's not the SQL I want to talk about, it's
steps. We get the balance of the joint account. We reach into the table. Okay, joint is
$10,000.
Conflicts and Isolation
We then get balance of Alice's account which is $50.
Then we'll perform an operation to update the balance of the joint account to what I just
retrieved which was $10,000 minus $1,000.
So we're reaching into the database, setting it to $9,000, and then we'll reach back in and
update the balance of Alice's account to $1,050.
All well and good. We've subtracted $1,000 from joint. We've added $1,000 to Alice.
Conflicts and Isolation
Then one day, they both log on to the website at the same time and both attempt to do
this move.
Alice starts her operation.
She reads the balance of the joint account. It's $10,000.
Conflicts and Isolation
Bob starts his. He reads the balance of the joint account. It's also $10,000.
No changes have happened yet.
Conflicts and Isolation
Alice gets her balance, which is $50.
Bob gets his balance, which is $45.
Conflicts and Isolation
Then Alice issues the update to joint.
She'd retrieved it as $10,000.
She will subtract $1,000 from that, which is $9,000, she changes the database.
Conflicts and Isolation
Bob also tries to perform that operation. He believes that joint account is $10,000. So he
will also set that to $9,000.
Then Alice updates her account adding $1,000 to it.
Bob updates his adding $1,000 to that.
Now the end result is that the joint account is down $1,000, but both Alice and Bob's
account have been credited $1,000 each. The balance should be $8,000, not $9,000.
Conflicts and Isolation
Now Alice and Bob might be perfectly happy with this, but the bank would not. This is what
often called a race condition. It's a conflict where two people or two programming
threads are doing very similar steps and just getting a little bit ahead of each other.
They're trying to affect the same data.
When they're doing it together, you'll end up with a very different outcome to what would
have happened if they done exactly the same steps, but Alice did all hers first and then Bob did
his second.
Transactions
So the first step to fixing this situation as we make these atomic. We're making these several actions grouped into one indivisible unit
by making them transactions. We do this by adding SQL keywords at the start and end. In this case, we use the words Begin
Transaction. We'll do all the operations we want to do and then we write the SQL keyword commit to say our transaction is done.
Now, these are SQL keywords that deal with transactions, although you will find that you write slightly different code to create a
transaction in the different database management systems. Sometimes it's just Begin, sometimes it's just Begin Work, sometimes
it's Begin Transaction or Start Transaction (as in MySQL), but again it's the core concept we're going for here.
We want to go all the way through and commit if everything is successful. If there's a problem anywhere in the transaction, we want
the database to roll
back to the start of the transaction as if no part of it had ever happened.
Transactions
I said this is step one, because this by itself may not help our race condition problem. If both of these transactions are still executing
at exactly the same time and they're allowed to do that, because we didn't actually have any errors occur.
The issue is that Bob is being allowed to read from the same table that Alice is in the process of changing, but she hasn't finished changing it
yet. This is often what's referred to as a dirty read. There's a transaction going on that's partway through it. It's going to be changing
this data, but we're allowed to read from somewhere in the middle of that transaction.
locking
simultaneously changing the same data at least until the transaction is over.
Just making the transaction is not good enough. We also want some kind of
to occur so that we are
prevented
from
Pessimistic Locking
pessimistic locking where we are pessimistic that transactions will conflict with
each other. So as soon as a transaction starts, we're going to lock the data that it's referring to, and only unlock it once the transaction
Now one way of doing this is what's referred to as
commits.
Here's how this would work.
Going back to the beginning situation we are creating transactions for both Alice and Bob.
So Alice's transaction starts. She reads the balance of the joint account and she will lock that information.
Now Bob would then attempt to read in his transaction, but he won't be allowed to. He'll be refused.
Pessimistic Locking
Hopefully only have milliseconds are going on here.
Bob is waiting, Alice goes ahead. She gets the balance of her account. She reaches in and changes the
joint account to $9,000. She then updates her account and then hits commit. Her transaction is over,
and that means any lock that she was applying is now unlocked.
Bob will now get that message back. The balance is $9,000.
Pessimistic Locking
Bob is allowed to continue to get his balance. He can subtract $1,000 from that. We're down to $8,000
as we should be. We update, and we commit his changes.
The thing is pessimistic locking is great for Alice, but not necessarily so great for Bob.
Optimistic Locking
One option with some database systems is to allow what's called optimistic locking, which means
Bob would be allowed to read from the table while the transaction is going on, because we are optimistic
that there won't be a conflict.
We begin with the same situation. Alice and Bob both start.
Alice starts her transaction and reads the joint account balance.
Bob tries, but where he was refused a moment ago with pessimistic locking, he's going to be allowed to
right now. Both continue.
Alice will read hers, Bob will read his.
Optimistic Locking
Alice will change the joint account down by $9,000.
Now here's the difference. Bob will try and update that too. He thinks he is going to update it from $10,000 to $9,000.
What's going to happen with optimistic locking is the database will immediately realize that the contents of this row has changed since
he read it, and it's going to actually issue an error. It will detect that there was a dirty read, and this transaction can't go any further,
because it doesn't know how to handle that conflict. In most cases, it will just roll back to the beginning of the transaction.
Bob would get some kind of error that the application would need to deal with.
Optimistic Locking
Alice can continue on, because her transaction hasn't thrown any kind of issue. She can commit with her changes in place. When you're working
with locking, both pessimistic and optimistic, this is one of the areas where it's implemented a little differently across Database Management
Systems, although the principles are the same.
Now with some DBMS, you have specific keywords to explicitly
lock data that you're accessing. With others, there's default behavior, what's
called the default
isolation level of a database, meaning, do we naturally do pessimistic locking or optimistic locking or perhaps something inbetween. We will lock any inserts and updates in the transaction until they commit, but not the select, just the basic read.
Take a look at your chosen database management system when it comes time to do this.
MySQL Workbench
https://www.mysql.com/products/workbench/
MySQL Workbench Links & Tutorials
• MySQL Workbench: Introduction (YouTube)
• MySQL Workbench: Connecting to a Server and Troubleshooting (YouTube)
For those who are interested in giving MySQL Workbench a try, it will make
more sense after the ICE today using phpMyAdmin, since a lot of the
functionality is similar. A good rule-of-thumb, is to get good first at working
with phpMyAdmin, then give MySQL Workbench a try since it has more
bells-and-whistles and has a bit more of a learning curve.
27
BIT 275 ICE 07
Creating a Database and Tables and
Inserting Data with phpMyAdmin
28