why should a database transaction be atomic?. abort = removal of the updates of a transaction an...

Why should a database transaction be atomic?

ABORT = Removal of the updates of a transaction

An abort is implemented by a roll back recovery where the before images of the log-file are restored in the database tables.

What can initiate an automatic abort?

When should a programmer initiate an abort?

The Relaxed Atomicity Property:

Committing subtransaction

Root transaction

Nested atomic subtransactions:

Subtransactions of a compensatable subtransaction must also be compensatable.

Subtransactions of a retriable subtransaction must also be retriable.

Subtransactions of a pivot subtransaction must either be compensatable or retriable. Where would you recommend to use retriable

sub-subtransactions in E-commerce?



Root transaction

How would you implement relaxed atomicity in a real- time money transfer between two banks?



Root transaction

In E-commerce the following locations are involved:

Seller, Bank of Seller, Buyer, Bank of Buyer, and Card issuer.

In which locations should the compensatable, pivot and retriable subtransactions be executed?



Root transaction

How would you recommend to implement atomic data replication from a primary copy by using relaxed atomicity?

Implementation of retriable subtransactions:

Manage compensatable and pivot subtransactions

Remote Procedure Call (RPC)

Update Propagation (UP)Manage retriable subtransactions

In practice SOA services function as RPCs and may also be used to implement UP.

Atomicity in mobile computing:

How can SOA help in implementing mobile atomicity?

Isolation anomaliesoccurs when you brake locking rules:

-----------------------------------------------

Example of Lost update anomaly:History: r1(x)…w2(x)…w1(x)…c1

Read without

lock

Database1

Reread with exclusive

lock

2. Make corrections

3. Write

How can the Lost Update be prevented?

The anomalies allowed by the different isolation levels of SQL

Readlocks do not lock the index.Read does not exclude writes. (Only update locks exist).

Update locks do not exclude reads.

The history of Lost update anomaly:

Read without

lock

Database1

Reread with exclusive

lock

2. Make corrections

3. Write

Examples of Lost update anomaly:r1(x)…w2(x)…w1(x)…c1

1. Is ”the lost update anomaly” acceptable when the address of a customer is changed?

2. Is ”the lost update anomaly” acceptable when the amount of available airplane tickets are reduced by B2C e-commerce?

3. Is ”the lost update anomaly” acceptable when the diagnose of a patient is changed?

4. Is the ”lost update anomaly” possible when the diagnose of a patient is created?


-----------------------------------------------

The history of Dirty read anomaly:

Examples of Dirty read anomaly:w1(x)…r2(x)…c2…a1...

1. Is the dirty read anomaly acceptable when the address of a customer is changed?

2. Is the dirty read anomaly acceptable when the amount of available airplane tickets are red by B2C e-commerce customer?

3. Is the dirty read anomaly acceptable when you want to read the diagnose of a patient?

4. Is the dirty read anomaly acceptable if you want to change the diagnose of a patient?

5. Is the dirty read anomaly acceptable when you have to decide whether to give a loan to a customer?


-----------------------------------------------

The history of Fuzzy read anomaly =Non-repeatable read anomaly:

Examples of Non-repeatable read anomaly:r1(x)…w2(x)… c2…r1(x)….

1. Is the Non-repeatable read anomaly acceptable when the address of a customer is changed?

2. Is the Non-repeatable read anomaly acceptable when the amount of available airplane tickets are reduced by B2C e-commerce?

3. Is the Non-repeatable read anomaly acceptable when you want to read the diagnose of a patient?

4. Is the Non-repeatable read anomaly acceptable if you want to change the diagnose of a patient?

5. Is the Non-repeatable read anomaly acceptable when you have to decide whether to give a loan to a customer?


-----------------------------------------------

The history of Phantom anomaly:

Examples of Phantom anomaly:r1 (S)…w2(y now in S)…c2…r1 (S)…

1. Is the Phantom anomaly acceptable when the address of a customer is changed?

2. Is the Phantom anomaly acceptable when the amount of available airplane tickets are reduced by B2C e-commerce for one of the airline companies?

3. Is the Phantom anomaly acceptable when you want to read the health records of a patient?

4. Is the Phantom anomaly acceptable if you want to change the diagnoses of a patient?

5. Is the Phantom anomaly acceptable when you have to decide whether to give a loan to a customer?

End of session

Thank you !!!Thank you !!!

Anomalies between a record x and its replica y:

The Read skew anomaly illustrates that that a shared lock (read lock) does not exclude writes globally:Read skew: r1(x)…w2(x)… w2(y)…c2… r1(y) …...

The Write skew anomaly illustrates that that an exclusive lock (write lock) does not exclude writes globally:Write skew: r1(x)… r2(y)… w2(y)…w1(x)…c1…c2.

The Asynchronous replication anomaly illustrates that that a exclusive lock (write lock) does not exclude reads globally:Asynchronous replication anomaly: w1(x).. r2(y)..c2..w1(y).. c1.

The general read skew anomaly:Read skew: r1(x)…w2(x)… w2(y)…c2… r1(y) …...

The read skew anomaly is a situation where a first transaction reads a record. Next, a second transaction updates the record and a related record. The updates are committed. Finally, the first transaction reads the related record. In this situation, the first transaction may find that the integrity rules of the database have been violated by the changes made by the second transaction.

The general write skew anomaly:

Write skew: r1(x)… r2(y)… w2(y)…w1(x)…c1…c2.

The write skew anomaly is a situation where two transactions both read two different related records. Next, the two transactions each update one of the two related records. Finally, both transactions commit. If a constraint has existed between the two related records, it might have been violated.

Implementation of distributed CSCW:•Describe compensatable, pivot, and retriable subtransactions for the most important update transactions

•Describe the countermeasures recommended for the most important transactions

Objectives for a DDBMS(Distributed DataBase Management System):

Distribution transparency, that is

Replication transparencyDistributed optimizerDistributed ACID propertiesHomogeneity as Heterogeneity is not in the marked

Evaluation criteria

Distribution architectures

Synchronous distributed database management system (DDBMS)

Central database with distributed clients

Multidatabases with flexible transactions.(Relaxed ACID properties)

Hot backup possibility

n-safe and mirroring Only mirroring is possible

0- safe, 1 safe and mirroring

Read performance/capacity

Best Worst Average

Write performance Worst Average Best

Blocking possibility Yes No no

Ease of failure recovery

Worst (The systems are very complex)

Best Best

Disaster recovery Best Worst Average

The probability of lost data[1]

Best.pn

Worstp

Average

Transaction logging Not supported Not supported Recommended

Availability[2] 1-qn 1-q 1-qn

Atomicity Best Best Best

Consistency Best Best Worst

Isolation Best Best Worst

Durability Best Best Best

Develop-ment costs Best Best Worst

Distributed DataBase Management System(DDBMS)TM = Transaction Manager.DM = Data Manager.

Distribueret data dictionary:

Global user views

Other locations

Distribution schema

Global conceptual view

Fragmentation schema

Allocation schema

Server

Local schema

Other locationsLocal conver-sion schema

Local conver-sion schema

Local schema

Server

Distributed DataBase Management System(DDBMS)TM = Transaction Manager.DM = Data Manager.

Homogenious DDBMS.

TM dictio-nary

DM dictio-nary

Heterogenious DDBMS.

TM dictio-nary

TM dictio-

nary

Conver-sion schema

Commit by using distributed 2PC:

Abort with distributed 2PC

why should a database transaction be atomic?. abort = removal of the updates of a transaction an...

Documents