transaction processing, Summaries of Distributed Database Management Systems

Download transaction processing and more Summaries Distributed Database Management Systems in PDF only on Docsity!

Chapter 7

Transaction Management

A transaction is a logical unit of database processing, which can

include one or more database operations, such as insertion,

deletion, modification, or retrieval operations.

Transaction processing systems are systems with large

databases and hundreds of concurrent users.

Must be either entirely completed or aborted. No intermediate

states are acceptable.

A Transaction

Commit

Saves changes made.

BEGIN;

INSERT INTO trans (id,item,quantity) VALUES (NULL,'ink','24');

INSERT INTO trans (id,item,quantity) VALUES (NULL,'pointer','4');

UPDATE trans SET item='HardDisk' WHERE id=3;

COMMIT;

Try the following:

BEGIN;

INSERT INTO trans (id,item,quantity) VALUES (NULL,'tablet','24');

INSERT INTO trans (id,item,quantity) VALUES (NULL,'ipad');

UPDATE trans SET item='notepad' WHERE id=4;

COMMIT;

Notice it will reject all statements even if the first one was correct.

A consistent database state is one in which all data integrity

constraints are satisfied.

• Example: during transaction, no other transaction must access

X.

• Register credit sale of 100 units of product X to customer Y

for $

• Consistent state only if both transactions are fully completed
• DBMS doesn’t guarantee transaction represents real-world

event

Example II Transaction



T1:



UPDATE PRODUCT

SET PROD_QOH = PROD_QOH - 100

WHERE PROD_CODE = ‘X’;



T2:



UPDATE ACCT_RECEIVABLE



SET ACCT_BALANCE = ACCT_BALANCE + 500

WHERE ACCT_NUM = ‘Y’;

• Atomicity - All transaction operations must be completed - Incomplete transactions aborted

• Durability

   Permanence of consistent database state 

  • Consistent state is permanently kept
• Serializability - Conducts transactions in serial order - Important in multi-user and distributed databases
• Isolation - Transaction data cannot be reused until its execution

complete

Transaction Properties

Roll back

• When the server encounters the rollback statement, it

discards all changes in the transaction and returns the

data to the state it was in before work began.

begin transaction

use emp

Insert employee values (‘600', 'Jane', 4,’A‘,’F’)

Insert employee values (‘700', 'John', 7,’B‘,’M’)

Update employee set empname = 'Mary' where Empid = ‘700‘

rollback transaction

Notice it will reject all statements even if they were all correct

• Create the following innodb table with My SQL
• The standard (MyISAM) table type is ideal for website use, where there

are many reads in comparison to writes, and no transactions.

• Where these conditions do not apply the InnoDB table is likely to be the

table type of choice.

CREATE TABLE trans

(

id int not null auto_increment,

item varchar(30) not null,

quantity int not null,

primary key(id)

)type=innodb;

Example 2

BEGIN;

UPDATE trans SET quantity =quantity + 10 WHERE id=1;

UPDATE trans SET quantity =quantity + 10 WHERE id=2;

UPDATE trans SET quantity =quantity + 10 WHERE id=10;

COMMIT;

Example 3

BEGIN;

INSERT INTO trans (id,item,quantity) VALUES (NULL,'printer',‘24');

INSERT INTO trans (id,item,quantity) VALUES (NULL,'USB');

INSERT INTO trans (id,item,quantity) VALUES (NULL,'DVD','33');

COMMIT;

consistent reading - Reads committed

data only

Window 1

INSERT INTO trans (id,item,quantity) VALUES

(NULL,'switch','200');

select * from trans;

Window 2

select * from trans

-> Window 2 shows changes because Autocommit = 1 by

default

• Window 1

BEGIN;

UPDATE trans SET item =‘mouse’ WHERE id=2;

SELECT * FROM trans;

ROLLBACK;

• Window 2

select * from Trans;

• -> Window 2 shows changes because we committed

• Tracks all transactions that update database
• May be used by ROLLBACK command
• May be used to recover from system failure

• Log stores

   Record for beginning of transaction 

  • Each SQL statement

Operation type (retrieve, update, insert, delete)

Names of objects

Before and after values for updated fields

Pointers to previous and next entries

• Commit Statement

Transaction Log

• Coordinates simultaneous transaction

execution in multiprocessing database

• Ensure serializability of transactions in

multiuser database environment

• Potential problems in multiuser environments 1. Lost updates 2. Uncommitted data 3. Inconsistent retrievals

Concurrency Control

7

1. Lost Updates Problem



T1 PROD_QOH = PROD_QOH + 100



T2 PROD_QOH = PROD_QOH - 30