Transcript Chapter 12: Managing Multi

Database Processing
Chapter 9
Managing Multi-user Databases
Chapter 9
Multi-User Issues
•
•
•
•
Concurrency Control
Database Reliability
Database Security
Database Administration
Multi-User Databases
Chapter 9
• Database usage
– Must plan for managing transactions
Transactions
Chapter
9
Chapter
9
Trans 6
Trans 4
Trans 3
Trans 5
Trans 7
Trans 1
Trans 2
(TIME)
Finite State Machine: exam question review process
Version 2
Question
Submitted
by author
Review
Get
Reviews
Re-assign LU to Skill
Final Edit
Send to Beta
Release for
review
Reject
Question
Reject
Author
Review
Final
Edit
Question
Edit
Generate
Send back
Question
Review
Assign
to Author
Course/LU
Author Edit
Chapter 9
Send to
Final Edit
Reject
Question
Question
has
Problem
Send to Exit
Send to Review
Beta Exam
Generate Beta
Stats
Beta Edit
Question
Put on
Beta Exam
Put on Exit Exam
Problem
Send to Review
Exit Exam
Reject
Question Send to
Exit
Generate Exam
Stats
Send
to Beta
Retire
Question
Retired
Transactions occur concurrently.
Lock Granularity
Chapter 9
Chapter 9
Remedy for inconsistencies caused
by concurrent processing
• Resource Locking
“disallow sharing by locking data that
are retrieved for update”
Chapter 9
Lock Terminology
•
•
•
•
•
Implicit locks placed by the DBMS
Explicit locks placed by command
Lock granularity the size of the lock
Exclusive lock from access of any type
Shared lock from change but not read
Lock Granularity
Chapter 9
• Lock granularity the size of the lock
• Can lock at the row-level, key (index),
page, Table, or Database level
• There is a trade-off between cost of
system overhead and concurrency
– Concurrency cost: restricted access to
object; some transactions may have to wait.
Lock Mode
Chapter 9
• Share lock (read only)
– Shared locks allows concurrent
transactions to read (SELECT) a resource.
No other transactions can modify the data
while shared locks exist on the resource.
Shared locks on a resource are released as
soon as the data has been read.
• Exclusive lock
– Denies access of any kind to concurrent
transactions
Chapter 9
Serializable Transactions
“a scheme for processing concurrent
transactions”
• Two-phased locking
– Growing and Shrinking phase
– Once a lock is released, transaction can
not place any more locks.
Serializable
Chapter
10
Chapter
9
Even though the transactions are
physically executed concurrently in
time…
Trans 6
Trans 4
Trans 3
Trans 5
Trans 7
Trans 1
Trans 2
(TIME)
Serializable
Chapter
10
Chapter
9
The logical result is the same as if they where
executed in sequence, one after the other.
• We affect this through adjusting the
transaction isolation level and resource
locking.
Trans 1 Trans 2 Trans 3 Trans 4 Trans 5 Trans 6 Trans 7
(TIME)
Serializable
Chapter 9
• To avoid a lost update, transactions must be isolated
at the Repeatable read level or higher.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
UPDATE QUERYSUBMISSION
SET SQLSUBMISSION = ‘/*Basic structure of CREATE…’
WHERE QUERYID = 84
COMMIT TRANSACTION
Transaction
Chapter 9
• Concept of a Transaction
– A series of Reads and Writes to the
database
– A transaction is a sequence of operations
performed as a single logical unit of work.
A logical unit of work must exhibit four
properties, called the ACID (Atomicity,
Consistency, Isolation, and Durability)
properties, to qualify as a transaction:
– Part of a broader area: Transaction
Management
Source: Microsoft documentation
ACID
Chapter 9
• Atomicity
– A transaction must be an atomic unit of
work; either all of its data modifications
are performed, or none of them is
performed
Chapter 9
Chapter 9
Chapter 9
Chapter 9
ACID
Chapter 9
• Transaction boundaries
• Controlled at the connection level
• BEGIN & COMMIT
BEGIN TRANSACTION
Marks the starting point of an explicit,
local transaction.
Syntax
BEGIN TRAN [ SACTION ] [ transaction_name |
@tran_name_variable
[WITH MARK [ 'description' ] ] ]
ACID
Chapter 9
• COMMIT TRAN
– A COMMIT statement guarantees all of the
transaction's modifications are made a
permanent part of the database.
– A COMMIT also frees resources, such as
locks, used by the transaction.
ACID
Chapter 9
• BEGIN TRANSACTION represents a
point at which the data referenced by a
connection is logically and physically
consistent.
• If errors are encountered, all data
modifications made after the BEGIN
TRANSACTION can be rolled back to
return the data to this known state of
consistency.
ACID
Chapter 9
• ROLLBACK TRAN
– Rolls back an explicit or implicit transaction to the
beginning of the transaction, or to a savepoint
inside a transaction.
– It also frees resources held by the transaction.
– A ROLLBACK TRANSACTION statement does not
produce any messages to the user. If warnings are
needed in stored procedures or triggers, use the
RAISERROR or PRINT statements. RAISERROR
is the preferred statement for indicating errors.
– ROLLBACK is expensive…
ACID
Chapter 9
• ROLLBACK TRAN
– Rollback and rollforward are possible
because the dbms maintains transaction
logs.
– You can view SQL Server transaction logs
with the undocumented Database Console
command:
DBCC log ({dbid|dbname}, [, type={-1|0|1|2|3|4}])
DBCC log (NorthWind, type=2)
ACID
Chapter 9
• savepoint
– A savepoint allows you to rollback a portion
of a transaction rather than the entire
transaction
– Savepoints allow you to commit part of a
transaction while rolling back the
remainder.
– A bit risky though. There is still the
chance that an error affects a change
made prior to the savepoint.
ACID
Chapter 9
• Consistency
– When completed, a transaction must leave
all data in a consistent state. In a
relational database, all rules must be
applied to the transaction’s modifications
to maintain all data integrity.
ACID
Chapter 9
• Consistency is maintained by rolling
back the transaction to the point where
it started (or locking resources).
ACID
Chapter 9
• Consistency
– Another issue is whether the transaction
will be able to see it’s own changes
• It is possible to write statements that cause
rows to be excluded by a subsequent SELECT
statement.
• @@Identity provides access to identity value
created by INSERT statement.
• However, it is not always that simple…
ACID
Chapter 9
(An excerpt from dbo.InsertSkillQuestion
ALTER PROC dbo.InsertSkillQuestion
:
:
BEGIN TRAN
BEGIN
:
:
INSERT INTO tblSkillQuestion
(LuSkillID, attribute list…)
VALUES
(@LuSkillID, @Question, variable list…)
Creates an identity
value that is stored in
@@Identity.
--We have to use SCOPE_IDENTITY() because @@Identity returns the
--last identity value. The last identity value is
--the row inserted into the log file generated by trigInsertSkillQuestionLog
DECLARE @LastIdentity AS int
SET @LastIdentity = SCOPE_IDENTITY()
--Insert row in status log file
EXEC InsertlogSkillQuestionStatus @LastIdentity, variable list…
END
IF @@ERROR <> 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
END
The value of @@Identity is not
visible to the EXEC
InsertLogSkillQuestionStatus.
ACID
• Isolation
Chapter 9
– Modifications made by concurrent transactions
must be isolated from the modifications made by
any other concurrent transactions. A transaction
either sees data in the state it was in before
another concurrent transaction modified it, or it
sees the data after the second transaction has
completed, but it does not see an intermediate
state. This is referred to as serializability
because it results in the ability to reload the
starting data and replay a series of transactions
to end up with the data in the same state it was in
after the original transactions were performed.
– We’ll look at example problems shortly
ACID
Chapter 9
• Durability
– After a transaction has completed, its
effects are permanently in place in the
system. The modifications persist even in
the event of a system failure.
Time Travel...
Chapter
7
Chapter
9
“Can a cube that does not last for any time at all, have a real existence?”
H.G. Wells: The Time Machine (1895)
Chapter 9
Deadlock
Locking solves one problem, but
introduces another…
“deadly embrace”; each transaction
waiting for a resource that the other
person has locked
Chapter 9
Page 314