- You have insert
and
update
SQL commands that make changes to the data
in a database.
- A database system is designed to support
a large number
simultaneous users
- A very interesting problem arrises when multiple users update the database simultaneously
- A update operation
is not executed as
one atomical operation,
but rather
in a
number (sequence) of operations
- Example:
UPDATE employee SET salary = salary + 1000 WHERE fname='John' and lname='Smith'
is executed as follows:
- Read the salary value of 'John Smith'
- Compute salary + 1000
- Write the new salary value back to the database
- A pathelogical example:
- Suppose two persons A and B have
a join account
(account #12345678)
- A deposts $1000 while atthe same time B withdraws $100
The following 2 update operations are performed simultaneously :
UPDATE_1: UPDATE checkingAccount SET balance = balance - 100 WHERE account_no = 12345678
UPDATE_2: UPDATE checkingAccount SET balance = balance + 1000 WHERE account_no = 12345678
- Suppose two persons A and B have
a join account
(account #12345678)
- A possible execution:
(assuming initial balance = $4000)
UPDATE_2: read balance ===> 4000 UPDATE_1: read balance ===> 4000 UPDATE_2: Compute balance + 1000 ===> 5000 UPDATE_1: Compute balance - 100 ===> 3900 UPDATE_2: Write balance ===> writes 5000 UPDATE_1: Write balance ===> writes 3900 (overwriting 5000 !!!)
- This is
not a fairy tale
Here is an example of a unsynchronized parallel execution of a multi-threaded program that exhibit this inconsistent behavior (taken from another course that I teach):
- Example Program:
(Demo unsynchronized parallel execution )
- Prog file: click here
Compile with: g++ -pthread -o thread02 thread02.C
Run it with: a.out 10, do it several times... you will see different answers each time...
The reason is that the variable N is updated (read, changed and written back) by multiple threads of executions (users if you will).
- We will not discuss the
general problem
of
simultaneous updates
in database systems here
(discussed later in "transaction processing")
Here, I just want to show you that updates made to the database are always tentative until the system can determine that the updates are "safe"
-
Every database update operation runs in 2 phases:
- Process phase
- Update operations are made
tentatively
(information are kept to undo the changes)
In this phase, the updates are not guaranteed to be finalized (i.e., that the updates may NOT be incorporated into the database).
- Update operations are made
tentatively
(information are kept to undo the changes)
- Commit phase
- The database system
determines whether
the updates made by the "transaction"
conflict with
updates made by other "transaction"
In this phase, the database system may cause one of more update operation to abort (due to irreconsiliable conflicts) !!!
- The database system
determines whether
the updates made by the "transaction"
conflict with
updates made by other "transaction"
- Process phase
- Open 2 connections
to MySQL and do the following
(watch the instructor in class because students do not have
permission to update the database...)
User 1 User 2 SELECT ssn, fname, lname, salary FROM employee WHERE fname='John' AND lname='Smith'; >> 123456789 John Smith 30000Don't do anything... Don't do anything... SET autocommit = 0; START TRANSACTION; UPDATE employee SET salary=31000 WHERE fname='John' AND lname='Smith'; >> 1 row updatedDon't do anything... SELECT ssn, fname, lname, salary FROM employee WHERE fname='John' AND lname='Smith'; >> 123456789 John Smith 31000 (Data in buffer has been updated !)SELECT ssn, fname, lname, salary FROM employee WHERE fname='John' AND lname='Smith'; >> 123456789 John Smith 30000 (Data in database has NOT been update yet !)Don't do anything... Don't do anything... COMMIT; >> Query OK, 0 rows affected (0.03 sec)
SELECT ssn, fname, lname, salary FROM employee WHERE fname='John' AND lname='Smith'; >> 123456789 John Smith 31000 (FINALLY !!!)Don't do anything...
| This experiment shows you that the updates made are not propagated to the database until transaction commits |
- Open 2 connections
to MySQL and do the following
(watch the instructor in class because students do not have
permission to update the database...)
User 1 User 2 SELECT ssn, fname, lname, salary FROM employee WHERE fname='John' AND lname='Smith'; >> 123456789 John Smith 30000Don't do anything... Don't do anything... SET autocommit = 0; START TRANSACTION; UPDATE employee SET salary=salary+1 WHERE fname='John' AND lname='Smith'; >> 1 row updatedDon't do anything... SELECT ssn, fname, lname, salary FROM employee WHERE fname='John' AND lname='Smith'; >> 123456789 John Smith 30001SET autocommit = 0; START TRANSACTION; UPDATE employee SET salary=salary+2 WHERE fname='John' AND lname='Smith'; >> updated HANGS !!!!!!!!!! (It's waiting for the other update to finish...) Outcome 1: after waiting a long time: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionDon't do anything... Don't do anything... COMMIT; // If you do this quick enough // the other update will finish ! >> releases Update 1 !!!Outcome 2: Commit after a short wait: SELECT ssn, fname, lname, salary FROM employee WHERE fname='John' AND lname='Smith'; >> 123456789 John Smith 30003 >> commit;Don't do anything...
| This experiment shows you that the updates made may need to wait to see it they are safe |
The lock time out is set to 50 sec by default....
Try: rollback !!!
- This section contains the
SQL command I use in class
to demo the
transaction processing
behavior:
Comment: use "cheung-sql" use companyDB SET autocommit = 0; /* Update now happens only on COMMENT */
SELECT ssn, fname, lname, salary /* Find John Smith's salary (30000) */ FROM employee WHERE fname='John' AND lname='Smith';
START TRANSACTION; UPDATE employee /* Resets John's salary to original value */ SET salary=30000 WHERE fname='John' AND lname='Smith'; COMMIT;
START TRANSACTION; UPDATE employee /* Update John's salary to new value */ SET salary=31000 WHERE fname='John' AND lname='Smith'; COMMIT;
START TRANSACTION; UPDATE employee /* Increase John's salary */ SET salary=salary + 1 WHERE fname='John' AND lname='Smith'; COMMIT;
- Transaction:
- Transaction processing =
an advanced programming technique
to ensure that
2 simultaneous updates to
the database:
- will appear to be
processes
in sequence
I.e.: the 2 updates appears to happen in sequence
- will appear to be
processes
in sequence
- Transaction processing =
an advanced programming technique
to ensure that
2 simultaneous updates to
the database:
- Transaction processing is
discussed in:
- CS554:
click here
It's a graduate course offered in Spring
Strong undergraduate students who has taken CS323 and CS377 can receive permission to take this course if you are interested
- CS554:
click here