CHAPTER 5 DATABASE TRANSACTION MANAGEMENT (5.1.6 PURPOSE OF CONCURRENCY…
CHAPTER 5 DATABASE TRANSACTION MANAGEMENT
5.1 TRANSACTION SYSTEM
UNIT OF WORK THAT IS PERFORM AGAINST DATABASE.
PROPAGATION OF ONE OR MORE CHANGES TO THE DATABASE
CAN BE MANUALLY BY USER OR AUTOMATICALLY BY DATABASE PROGRAM
EXAMPLE: TRANSACTION HAPPEN AFTER CREATE / UPDATE / DELETE TABLE
TO ENSURE THE DATA INTEGRITY AND TO HANDLE DATABASE ERRORS
5.1.2 TRANSACTING PROCESSING SYSTEM
BATCH TRANSACTION PROCESSING
IS THE PROCESSING/STORAGE OF DATA AT THE TIME OF THE EVENT WITH ACTUAL UPDATING OF THE DATABASE LATER WHEN IT IS SCHEDULED
CONTROL OVER TIME OF PROCESSING
REDUCE SETUP AND PROCESSING COAST
ERRORS CORRECTED AFTER THE PROCESSING OF DATA
TIME DELAY IN GATHERING DATA, STORING AND BULK PROCESSING
OPERATIONAL COAST MAY INCREASE - ONLY IDENTICAL DATA IS PROCESSED IN ONE BATCH
ON-LINE TRANSACTION PROCESSING SYSTEM (OLTP)
REFERS TO A CLASS OF SYSTEMS THAT FACILITIES AND MANAGE TRANSACTION-ORIENTED APPLICATION
IT PROVIDES FASTER AND MOVE ACCURATE FORECAST FOR REVENUE AND EXPENSES
IT PROVIDES A CONCREATE FOUNDATION FOR A STABLE ORGANIZATION BECAUS OF TIMELY MODIFICATION OF ALL TRANSACTION
IT MAKES THE TRANSACTION MUCH EASIER ON BEHALF OF THE CUSTOMERS BY ALLOWING THEM TO MAKE THE PAYMENT ACCORDING TO THEIR CHOICES
IT BRODEANS THE CUSTOMER BASE FOR AN ORGANIZATION BY SIMPLIFYING AND SPEEDING UP INDIVIDUAL PROCESSES
IT MAKES THE DATABASE MUCH MORE SUSCEPTIBLE TO INTRUDERS AND HACKERS BECAUSE IT MAKES THE DATABSE AVAILABLE WORLWIDE
AS SIMPLE AS OLTP IS, THE SIMPLEST DISRUPTION IN THE SYSTEM HAS THE POTENTIAL TO CAUSE A GREAT DEAL OF PROBLEMS,CAUSING A WASTE BOTH TIME AND MONEY
IT CAN LEAD TO SERVER FAILURE, WHICH MAY CAUSE DELAY OR EVEN WIPE OUT LARGE AMOUNT OF DATA FROM THE DATABASE
REAL TIME TRANSACTION PROCESSING SYSTEM
AS THE IMMEDIATE PROCESSING OF DATA WITH THE DATABASE UPDATED AS THE TRANSACTION IS BEIONG CARRIED OUT.
EXAMPLE : BANK ATM , POS TERMINAL
5.1.3 PROPERTIES OF DATABASE TRANSACTION
The database must remain in a consistent state after any transaction. No transaction should have any adverse effect on the data residing in the database. If the database was in a consistent state before the execution of a transaction, it must remain consistent after the execution of the transaction as well.
The database should be durable enough to hold all its latest updates even if the syatem fails or restarts. if a transaction updates a chunk of data in a database and commits, the the database willl hold the modified data. If a transaction commits but the system fails before the data could be written on to the disk, then the data will be updated once the system springs back into action
This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. There must be no state in a database where a transaction is left partially completed. States should be defined either before the execution of the transaction or after the execution/ abortion/ failure of the transaction.
In a database sytem where more than one transaction are being executed simultaneously and it parellel, the prperty of isolation states that all the transaction will be carried out and executed as if the only transaction in the system. No transaction will affect the existence of any other transaction.
5.1.4 START TRANSACTION AND COMMIT STATEMENTS
THE START TRANSACTION COMMAND
To start a transaction, you use the START TRANSACTION statement. The BEGIN or BEGIN WORK is the aliase of the START TRANSACTION. - START TRANSACTION or BEGIN start a new transaction.
THE COMMIT COMMAND
The COMMIT command is the transactional commad used to save changes invoked by a transaction to the database.
The COMMIT command saves all the transactions to the database since the last COMMIT or ROLLBACK command.
The syntax for the COMMIT command is as follows
5.1.5 CONCURRENCY CONTROL
Concurrency control means synchronizing or coordinating correctly the execution of multiple transactions in a multi-database environment.
5.1.6 PURPOSE OF CONCURRENCY CONTROL
If transactions are executed sequentially with no overlap in time, no transaction concurrency exists.
Lost Update problem occurs when two transactions that access the same database items have their operations interleaved in a way that makes the value of some database item incorrect.
Uncomitted Data happens when processing two transactions concurrently might lead to uncomitted data.
Inconsistent Retrieval occurs when a trasaction calculates an aggregate or summary function over a set data with the other transaction updating
The Scheduler is series of operations from one of more transactions. A schedulue can be of two types.