Unit 9Unit 9

Transaction ProcessingTransaction Processing

Key ConceptsKey Concepts• Distributed databases and DDBMS• Distributed database advantages.• Distributed database disadvantages• Using transactions• Identifying multiuser problems• Detecting and clearing deadlocks• Implementing locking• Implementing transaction isolation

Distributed Database TermsDistributed Database Terms• Distributed database

– Collection of shared data distributed over multiple databases in a computer network.

• Distributed DBMS– Software designed to manage and

maintain distributed databases.• Distributed processing

– Application processing that involves multiple servers.

Distributed Database Distributed Database SampleSample

• Database Systems, Example 22.1, pg. 690

Distribute Database PointsDistribute Database Points• Global applications can access

multiple databases simultaneously.• Local applications can access

individual databases.• Data can be distributed across

multiple locations.• Locations can contain unique data,

duplicate data, or both.

AdvantagesAdvantages• Can be based on organization

structure• Improves data sharing while allowing

local autonomy.• Improves data availability and

reliability• Improves performance• Modular growth allows for cost-

effective, competitive implementations.

DisadvantagesDisadvantages• Complex implementations can be difficult

to maintain.• Most costly and less secure than

centralized data implementations.• Difficult to maintain data integrity across

sites.• Few standards exist and most database

administrators and programmers have minimal experience.

Data AllocationData Allocation• Centralized• Fragmented• Complete replication• Selective replication

TransactionsTransactions• MySQLSTART TRANSACTION

processing statementsCOMMIT | ROLLBACK• SQL ServerBEGIN TRANSACTION

processing statementsCOMMIT | ROLLBACK

Potential ProblemsPotential Problems• Dirty Read/Uncommitted Read

– A user sees data that has not been committed and might change.

• Nonrepeatable/Nonreproduceable Read– User reads from both clean and dirty data

and receives a result that changes on a subsequent read.

• Phantom Read– Occurs when new data becomes available

after a read.• Lost Update

– Data update is overwritten by another user.

LockingLocking• Protecting data from read, modification, or

both.• Locking typically occurs at the table or row

level, depending on the operation.• A shared lock prevents one user from

changing data while another user is reading from it.

• An exclusive lock prevents anyone else from accessing the data and is used while modifying data.

DeadlockDeadlock• User1 has locked Row1 and is waiting

for access to Row2.• User2 has locked Row2 and is waiting

for access to Row1.• Neither users' processes can

continue until one of the locks is cleared.

Standard Isolation LevelsStandard Isolation Levels• Serializable• Repeatable read• Read committed• Read uncommitted

Isolation Levels andIsolation Levels andMultiuser Access ProblemsMultiuser Access Problems

• Introduction to SQL, Table 27.1, pg. 742