How is Week 9 SQL Atomocity solved?

Question

David said:

So in problem set seven, we give you this one line of code, which long story short, solves this problem in MySQL. This very long instruction that doesn't even fit onto one line on the screen here ensures that your operation is what's called atomic. It all happens at once, or it doesn't happen at all. This very long phrase cannot get interrupted partially. And what it does is literally what it says. Insert into some table the following three fields those specific values, but on duplicate key, don't do an insert. Do an update. So this is like doing a SELECT and an INSERT so to speak at the same time. And what is the key that's probably being referred to here? It turns out, and you'll see this in problem set seven's spec, because we've declared there to be a unique key on this particular table such that you can't have multiple rows for the same user with the same penny stock symbol-- in this example here, DVN.V is a silly penny stock that we refer to in the spec. Because we've declared it to be unique, what this means is that if you try to insert a duplicate row, you're instead going to update it without anyone else having a chance to change the state of the world either. So in short, this ensures things are atomic.

But what exactly does this mean? How do a SELECT and INSERT and the same time help solve the problem? And what's with all the 'key' thing?

Also, he then said that the Start Transaction will fix the problem, making the transaction happen at the same time. WHY?

Answer 1

I think the professor was referring to the process of updating when he analogized this to a SELECT query and an INSERT query at the same time, because that's probably how you'd probably do an update manually — you would first retrieve (select) the data, update it, delete the original data from the table, and insert the updated data back.

I think this query is atomic as it executes as a whole or it doesn't execute at all. This solves the problem in the sense that you can't possibly affect the data in the table in a bad way or have errors due to various attempts of executing multiple separate INSERT/UPDATE queries at the same time.

You'll learn more about keys as you proceed through the pset specifications.

You may read more about transactions here!

Answer 2

Kareem correctly explained how the query works. I only would like to elaborate the concept of "ATOMICITY".

Since actual computers run in a multi-tasking, multi-user environment, care should be taken in avoiding concurrent modifications of the same data and possible deadlocks.

Let's take the following pseudocode as an example:

Process 1:
1 - Read the record having ID x.
2 - Make some CPU-intensive operations on the record, requiring a few seconds to complete.
3 - Save the modified record.

Process 2:
1 - Read the record having ID x.
2 - Quickly modify the time.
3 - Save the modified record.

If you are in a concurrent environment, odds are that the record with ID x is read by the first process, then modified by the second, and finally saved by the first, thus vanifying the update of the second process.

An operation is said to be atomic when the data it uses is locked and unavailable to other processes until the operation ends, thus assuring data integrity. Moreover, atomic operations look as one-line statements, but their code assumes multiple instructions.

The line

" INSERT INTO table (id, symbol, shares) VALUES(7, 'DVN.V', 10) ON DUPLICATE KEY UPDATE shares = shares + VALUES(shares); "

Is one single statement and is coded internally as an if/then statement:
if the key is not duplicated, insert the record
else update the record.

Using the SQL version ensures atomicity, while the if/else PHP construct does not assure that the multitasking engine lets another process free to insert the record after the if branch, thus making the insertion fail.

first process            second process
is the key present? No!
                         insert the key (success)
insert the key (failure>)