Transcript Transaction

CSC 411/511:
DBMS Design
Transations
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
1
Transaction
• Databases are all about sharing data, so it is
common for multiple users to be accessing and
even changing the same data at the same time.
• The simultaneous execution of operations is called
concurrency.
• Sometimes concurrency can get us into trouble if
our changes require multiple SQL statements.
Let’s look at an example.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
2
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
3
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
4
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
5
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
6
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
7
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
8
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
9
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
10
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
11
Example
• We always want to make sure that we have all of
the ingredients for every item before we submit a
customer’s order.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
12
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
13
• Now consider the following scenario: Two customers, Bob
and Mary, arrive at about the same time and each go to a
different register. Bob orders a Garden Salad. Bob’s
register runs Query 11.1, which indicates that there are
enough ingredients to make his order. While Bob decides if
he wants to order a drink, Mary also orders a Garden
Salad. Mary’s register runs Query 11.1, which again
indicates that there are enough ingredients to make her
order. Mary immediately pays for her order, and her register
executes Update 11.2.
• Bob finally decides against a drink, and his register
executes Update 11.2. Of course, we only have enough
ingredients to make one Garden Salad. This is a classic
problem in database systems; it is called the isolation or
serializability problem.
• In general, if two or more users access the same data and
one or more of the statements changes the data, we have a
conflict. If the users perform multiple steps, conflicts can
cause incorrect results to occur.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
14
Transaction
• To deal with this problem, databases allow the
grouping of a sequence of SQL statements into an
indivisible unit of work called a transaction.
• A transaction ends with either
– a commit or a rollback:
• Commit: commit permanently stores all of the
changes performed by the transaction.
• Rollback: rollback removes all of the updates
performed by the transaction, no matter how
many rows have been changed. A rollback can
be executed either by the DBMS to prevent
incorrect actions or explicitly by the user.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
15
ACID
• The DBMS provides the following guarantees for a
transaction, called the ACID properties:
– Atomicity: Either all (commit) or none (rollback) of the
changes within a transaction are made permanent.
– Consistency: If a transaction executes on a consistent
database, then when it terminates the database will still be
consistent.
– Isolation: A transaction can execute on a database as though
it is the only transaction running.
– Durability: Changes made by any committed transaction are
permanent, even surviving system crashes and hardware
failures.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
16
Solution for Bob’s problem
• For Bob and Mary, Query 11.1 and the Update
11.2 are not two independent operations on the
database but are actually part of a single order.
• The logical unit of work is both the query and the
update. We use transactions to let Bob and Mary
execute Query 11.1 and Update 11.2 as one unit of
work.
• This avoids any conflicting updates to the database
(either Bob, Mary, or neither gets the Garden
Salad, not both), and we know that if a transaction
commits, all of its changes are permanent.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
17
Ending a Transaction
• A transaction ends with either a commit or
rollback.
• A commit is explicitly executed by the user using
the COMMIT statement:
– COMMIT [WORK]
– COMMIT attempts to commit all of the changes made since
the beginning of the transaction.
– If a problem is detected, COMMIT signals an error, and the
transaction is rolled back. Once a commit successfully
completes, the changes are permanent.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
18
• Here’s a situation where a fear of commitment can
be healthy. A rollback can be executed either by
the DBMS (to prevent incorrect actions) or
explicitly by the user using the ROLLBACK
statement:
– ROLLBACK [WORK][TO SAVEPOINT <savepoint>]
– ROLLBACK undoes all of the changes made since the
beginning of the current transaction.
• If you have an active transaction and you kill your
query processor without executing a COMMIT, the
DBMS should roll back all of your changes.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
19
Starting a Transaction
• START TRANSACTION [<transaction
characteristics>]
• BEGIN TRANSACTION [<transaction
characteristics>]
• While a transaction is running, all of the data
accessed by the transaction are protected.
• For example, start a transaction and execute
Query 11.1. Now, in a separate session try to
execute Update 11.2. What usually happens is that
Update 11.2 does not complete.The database has
detected a possible conflict between Query 11.1
and Update 11.2. Whichever transaction tries to
access the data second is blocked by the atabase.
No operations can be submitted by that transaction
until the block ends, and that won’t happen until
the first transaction terminates.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
20
Auto-Commit
• Most DBMSs include an auto-commit mode where
a commit is automatically attempted after every
SQL statement.
• With auto-commit, all transactions consist of only a
single SQL statement.
• auto-commit is the default mode. Changing to the
manual-commit mode where a COMMIT statement
must be executed to commit is DBMS-specific.
Some DBMSs will temporarily suspend the autocommit mode if the user enters a START
TRANSACTION.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
21
Savepoints
• SQL allows you to create named placeholders,
called savepoints, in the sequence of statements in
a transaction. You can rollback to a savepoint
instead of to the beginning of the transaction. Only
the changes made after the savepoint are undone.
To set a savepoint, use the SAVEPOINT
command:
– SAVEPOINT <savepoint name>
• If we create a savepoint named sp, we can rollback
to that savepoint with the following:
– ROLLBACK TO SAVEPOINT sp
– Executing ROLLBACK without designating a savepoint or
executing a COMMIT deletes all savepoints back to the start
of the transaction. A rollback to a particular savepoint deletes
all intervening savepoints.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
22
• Suppose Bob is ordering food for his entire soccer
team, one player at a time, and the last player
wants to change his order. ROLLBACK will undo
the order for all players; however, if we created a
savepoint before each player’s order, we could
easily rollback just the order of the last player.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
23
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
24
Transaction Characteristics
• SET TRANSACTION <mode>[, <mode>]
– READ ONLY: only statements that do not change the data
are allowed.
– READ WRITE: both statements that access and
manipulate data are allowed.
– SERIALIZABLE: prevents all possible conflicts.
– REPEATABLE READ: allows a problem known as
phantom read. A phantom read happens when a transaction
reads a set of rows (such as an entire table). A second
transaction then inserts a row into the table. If the first
transaction repeats the read, the results will now be different.
– READ COMMITTED
– READ UNCOMMITTED
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
25
Locking Issues
• Deadlock and lock escalation
• When a deadlock occurs in any computer system,
progress cannot occur. For example, if two
transactions execute Query 11.1 at the same time,
the database will prevent any other transaction
from changing the values read. Now suppose the
first transaction tries to execute Update 11.2. The
DBMS will block the transaction, preventing it from
doing any work. If the second transaction also
performs Update 11.2, then it will also be blocked.
• Neither transaction can make progress. They are
deadlocked.
• DBMS will detect this deadlock and rollback one of
the transactions, allowing the other to make
progress.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
26
Lock Escalation
• Lock escalation occurs as a result of a transaction
updating a large amount of data in a single table.
Usually, a DBMS will only lock as much of a
database as needed for the transaction to perform
its operations. However, if a transaction updates a
large portion of a table, the DBMS may lock the
entire table. This will help the performance of the
update transaction, but it may cause serious
performance problems for concurrent transactions,
because they will not be able to read any of the
table.
• The solutions to the lock escalation problem are
application specific and include dividing the update
transaction into independent transactions, running
the transactions under a reduced isolation level, or
accepting the reduced performance.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
27
Summary
• A transaction is a logical unit of work.
• By combining multiple SQL statements into a
single transaction we can execute many complex
statements as though they are a single statement.
• This allows us to correctly update different tables
at one time. Transactions can either commit, which
means that all of the operations are saved to the
database, or rollback, which means all of the
operations are removed from the database.
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
28
Exercise
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
29
CSC 411/511:
DBMS Design
Questions?
Dr. Nan Wang
CSC411_L12_JDBC_MySQL
30