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
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.
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 Commands
•
•
•
•
•
Begin Transaction
Update commands
Commit
RollBack
End Transaction
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
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.
Oracle Table Lock
• 1. When DML commands are issued, Oracle
implicitly places a shared lock on rows being
affected, so no other user can change the same
rows. When DDL commands are issued, Oracle
implicitly places an exclusive lock on the table.
• 2. A user can explicitly lock a table by a LOCK
TABLE command:
– LOCK TABLE tablename IN SHARE MODE;
– LOCK TABLE tablename IN EXCLUSIVE MODE;
• Note: Lock will be released when the user issues
a ROLLBACK or COMMIT command, or when
the user exits the system.
SELECT … FOR UPDATE
• Example:
– SELECT * FROM Employee
– WHERE EID=‘E5’
– FOR UPDATE;
• This command places a shared lock on selected
records and let the user to view the records
when it is anticipated that they will need to
update.
• The lock is released after a UPDATE command
is used or by a ROLLBACK or COMMIT
command.
Oracle SQL Commit
• Autocommit
– On
– Off
• Rollback
• Commit
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.
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.
Database Security
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)
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.
• 1. Retrieve all records:In the textbox, enter:
‘ OR 1=1 OR CID = ‘
2. Guess table name or field name:
‘ AND 1=(SELECT COUNT(*) FROM Orders) AND
CID=‘
3. Finding some users:
' or cname like 'S%' or cid=‘
SQLInjectionDemo
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