Transcript Concurrent Control

Concurrency Control
User 2
Read
User 1
Write
Read
R/R
R/W
Write
R/W
W/W
R/W: Inconsistent Read problem.
W/W: Lost Update problem.
Example
• Husband/Wife joint account with $1000
balance.
• Transactions:
– Husband: Withdraw 800
– Wife: Withdraw 100
• Processing:
– Read Balance, Calculate New Balance, Write New Balance
Husband:ReadBalance
1000
Wife:
CalNewBalance WriteNewBalance
(In memory)
(On disk)
New=1000-800
200
ReadBalance
CalNewBalance WriteNewBalance
1000
New=1000-100
900
Serializable Schedule
• Serial Schedule: The operations of each concurrent
transaction are executed consecutively without any
interleaved operations from other transactions.
• Nonserial Schedule: The operations from a set of
concurrent transactions are interleaved.
– Maximizing database availability
• Serializable schedule: If a set of transactions executes
concurrently, the nonserial schedule is called serializable
if it produces the same results as a serial schedule.
Locking
• Locking is the most widely used approach
to ensure serializability of concurrent
transactions.
• Shared lock: read only access
• Exclusive lock: for both read and write
access.
Two-Phase Locking
• A transaction must acquire a lock (read or
write) on an item before operating on the
item.
• Once the transaction releases a lock, it
cannot acquire any new locks.
• All locking operations precede the first
unlock operation in the transaction:
– Growing phase
– Shrinking phase
Lock Granularity
• The size of data items protected by a lock.
– Entire database
– Entire table
– A page
– A record
– A Field
• The coarser the data item size, the lower
the degree of concurrency permitted.
Dead Lock
• Two transactions wait for locks on items
held by the other.
DataItem 1
Lock
T1
Wait For
T2
Wait For
DataItem 2
Lock
Transaction
• An unit of work on database that is either
completed in its entirety or is not
performed at all.
Transaction ACID Properties
• Atomic
– Transaction cannot be subdivided
– All or nothing
• Consistent
– Constraints don’t change from before transaction to after
transaction
– A transaction transforms a database from one consistent state to
another consistent state.
• Isolated
– Transactions execute independently of one another.
– Database changes not revealed to users until after transaction
has completed
• Durable
– Database changes are permanent and must not be lost.
Transaction Commands
•
•
•
•
•
Begin Transaction
Update commands
Commit
RollBack
End Transaction
Read/
Write
Begin
Transaction
Commit
Partially
Committed
Active State
Committed
Abort
Abort
Failed
aborted
DefiningTransaction in An
Application
• Truck Rental System:
– Vehicle Table:
»
»
– VReservation:
»
VID, VType, VStatus
V1
V2
PickUp Available
TowTruck Booked
RID, VID, Date
R1
V2
1/2/04
Transaction Example
(Pseudo Code)
Sub Rent(RID, VID, RDate)
Begin Transaction
Insert (RID, VID, RDate) into VReservation table
If No Error Then
Update Vehicle Status
If No Error Then
Commit Transaction
Else
Roll Back
End if
Else
Roll Back
End if
End Sub
Log File (Journal)
• A file that contains all information about all
updates to the database. It may contain the
following data:
– Transaction records:
• Transaction ID
• Type of action:
– Begin, Insert,Delete, Modify, Commit, Rollback, End
• Before-image
• After-image
– Checkpoint records
• The point of synchronization between the database and the
transaction log file.
Completed Transaction
• All calculations done by the transaction in
its work space (RAM) must have finished,
and a copy of the results of the transaction
must have been written in a secure place
(log file). The action of committing the
transaction must also be written in the log.
Two Phase Commit
• A transaction cannot commit until it has
recorded all its changes in the log.
• A transaction cannot write into the
database until it has committed.
To Recover
• In the event of a failure, examine the log
starting from the most recent checkpoint
record.
• Any transaction with Transaction Start and
Transaction Commit records should be
redone:
– Perform all the writes to the database using
the after-image log records in the order in
which they were written to the log.
Back-up Facilities
• Automatic dump facility that produces
backup copy of the entire database
• Periodic backup (e.g. nightly, weekly)
• Cold backup–database is shut down
during backup
• Hot backup–selected portion is shut
down and backed up at a given time
• Backups stored in secure, off-site
location
Database Security
Database Security
• Database Security: Protection of the
data against accidental or intentional
loss, destruction, or misuse
• Increased difficulty due to Internet
access and client/server technologies
Threats to Data Security
• Accidental losses attributable to:
– People
• Users: using another person’s means of access, viewing
unauthorized data, introduction of viruses
• Programmers/Operators
• Database administrator: Inadequate security policy
– Software failure
• DBMS: security mechanism, privilege
• Application software: program alteration
– Hardware failure
• Theft and fraud
• Improper data access:
– Loss of privacy (personal data)
– Loss of confidentiality (corporate data)
• Loss of data integrity
• Loss of availability (through, e.g. sabotage)
Figure 12-3 Possible locations of data security threats
Countermeasures to Threats
• Authorization
– Authentication
•
•
•
•
•
Access controls: privileges
Database views
BackUp and Recovery
Enforcing integrity rules
Encryption
– Symmetric encryption:use same key for encryption and
decryption
– Asymmetric encryption:
• Public key: for encryption
• Private key: decryption
• RAID
Authorization Rules
• Controls incorporated in the data management
system
• Restrict:
– access to data
– actions that people can take on data
• Authorization matrix for:
–
–
–
–
Subjects
Objects
Actions
Constraints
Figure 12-5 Authorization matrix
SQL Injection
• "SQL Injection" is an
unverified/unsanitized user input
vulnerability, and the idea is to convince
the application to run SQL code that was
not intended.
• Exploits applications that use external
input for database commands.
SQL Injection Demo
• On a web page that takes customer ID entered
in a textbox as input, then displays the
customer’s data.
• In the textbox, enter:
‘ OR 1=1 OR CID = ‘
SQLInjectionDemo
Other SQL injection examples:
Demo
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source = c:\salesDB.mdb"
Dim objConn As New OleDbConnection(strConn)
Dim strSQL As String = "select * from customer where cid = '" &
TextBox1.Text & "'"
Dim objComm As New OleDbCommand(strSQL, objConn)
Try
objConn.Open()
Dim objDataReader As OleDbDataReader
objDataReader = objComm.ExecuteReader()
GridView1.DataSource = objDataReader
GridView1.DataBind()
Catch except As SystemException
Response.Write(except.Message)
End Try
End Sub