Concurrency Control

1
Concurrency Control
2
User 2
Read
Write
Read
User 1
Write
R/W Inconsistent Read problem. W/W Lost Update
problem.
3
Example

• Husband/Wife joint account with 1000 balance.
• Transactions
• Husband Withdraw 800
• Wife Withdraw 100
• Processing
• Read Balance, Calculate New Balance, Write New
  Balance

4
HusbandReadBalance CalNewBalance WriteNewBalance
(In memory) (On disk) 1000 New1000-800 200 W
ife ReadBalance CalNewBalance
WriteNewBalance 1000 New1000-100 900
5
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.

6
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.

7
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

8
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.

9
Dead Lock

• Two transactions wait for locks on items held by
  the other.

DataItem 1
Wait For
Lock
T1
T2
Lock
DataItem 2
Wait For
10
Transaction

• An unit of work on database that is either
  completed in its entirety or is not performed at
  all.

11
Transaction ACID Properties

• Atomic
• Transaction cannot be subdivided
• All or nothing
• Consistent
• Constraints dont 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.

12
Transaction Commands

• Begin Transaction
• Update commands
• Commit
• RollBack
• End Transaction

13
Read/Write
Committed
Partially Committed
Commit
Begin Transaction
Active State
Abort
Failed
aborted
Abort
14
DefiningTransaction in An Application

• Truck Rental System
• Vehicle Table VID, VType, VStatus
• V1 PickUp Available
• V2 TowTruck Booked
• VReservation RID, VID, Date
• R1 V2 1/2/04

15
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
16
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.

17
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.

18
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.

19
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.

20
Back-up Facilities

• Automatic dump facility that produces backup copy
  of the entire database
• Periodic backup (e.g. nightly, weekly)
• Cold backupdatabase is shut down during backup
• Hot backupselected portion is shut down and
  backed up at a given time
• Backups stored in secure, off-site location

21
Database Security
22
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

23
Threats to Data Security

• Accidental losses attributable to
• People
• Users using another persons 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)

24
Figure 12-3 Possible locations of data security
threats
25
Countermeasures to Threats

• Authorization
• Authentication
• Access controls privileges
• Database views
• BackUp and Recovery
• Enforcing integrity rules
• Encryption
• Symmetric encryptionuse same key for encryption
  and decryption
• Asymmetric encryption
• Public key for encryption
• Private key decryption
• RAID

26
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

27
Figure 12-5 Authorization matrix
28
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.

29
SQL Injection Demo

• On a web page that takes customer ID entered in a
  textbox as input, then displays the customers
  data.
• In the textbox, enter
• OR 11 OR CID
• SQLInjectionDemo
• Other SQL injection examples

30
Demo
Protected Sub Button1_Click(ByVal sender As
Object, ByVal e As System.EventArgs) Handles
Button1.Click Dim strConn As String
"ProviderMicrosoft.Jet.OLEDB.4.0Data 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