CHAPTER 5 : DATABASE TRANSACTION MANAGEMENT

CHAPTER 5 : DATABASE TRANSACTION MANAGEMENT

1. PROPERTISE OF DATABASE TRANSACTION

   • ATOMICITY
     • THIS PROPERTY STATES THAT A TRANSACTION MUST BE TREATED AS AN ATOMIC UNIT, THAT IS EITHER ALL OF IT OPERATION ARE EXECUTED OR NONE
   • ISOLATION
     • THE PROPERTY OF ISOLATION STATES THAT ALL THE TRANSACTION WILL BE CARRIED OUT AND EXECUTED AS IF IT IS THE ONLY TRANSACTION IN THE SYSTEM
   • DURABILITY
     • THE DATABASE SHOULD BE DURABLE ENOUGH TO HOLD ALL IT LATEST UPDATES EVEN IF THE SYSTEM FAIL TO RESTART
   • CONSISTENCY
     • THE DATABASE MUST REMAIN IN A CONSISTENT STATE AFTER ANY TRANSACTION .

2. TRANSACTION SYSTEM

   • A UNIT OF WORK THAT IS PERFORMED AGAINST A DATABASE
   • UNIT OF SEQUENCES OF WORK ACCOMPLISHED IN A LOGICAL ORDER

3. PURPOSE OF CONCURRENCY CONTROL

   • INCONSISTENT RETRIEVAL
     • OCCURS WHEN A TRANSACTION CALCULATES AN AGGREGATE OR SUMMARY FUNCTION (EXAMPLE: SUM) OVER A SET OF DATA WITH THE OTHER TRANSACTION ARE UPDATING
   • UNCOMMITED DATA
     • PROCESSING TWO TRANSACTIONS CONCURRENTLY MIGHT LEAD TO UNCOMMITED DATA
   • THE LOST UPDATE PROBLEM
     • OCCURS WHEN TWO TRANSACTION THAT ACCESS THE SAME DATABASE ITEMS HAVE THEIR OPERATIONS INTERLEAVED IN A WAY THAT MAKES THE VALUE OF SOME DATABASE ITEM INCORRECT
   • THE SCHEDULER
     • CONCURRENT SCHEDULE : WHEN OPERATION OF A TRANSACTION COMPLETELY EXECUTES BEFORE STARTING ANOTHER TRANSACTION
     • SERIAL SCHEDULER : WHEN ONE TRANSACTION COMPLETELY EXECUTES BEFORE STARTING ANOTHER TRANSACTION

4. CONCURRENCY CONTROL

   • SYNCHRONIZING OR COORDINATING THE EXECUTION OF MULTIPLE TRANSACTIONS IN MULTI-USER DATABASE ENVIRONMENT

5. DATABASE RECOVERY MANAGEMENT -: PLAN REDUCESTHE DATA RECOVERY TIME IF THE DATABASE ERROR OCCURS ON THE SERVER

   • DATABASE BACK-UP
     • LOGICAL BACK-UP - LOGICAL DATA WHICH IS EXTRACTED FROM DATABASE
     • PHYSICAL BACK-UP - BACKUPS OF PHYSICAL FILES USED IN STORING AND RECOVERING DATA
     • IMPORTANCE OF BACK-UPS
       • LOGICAL BACKUPS EXTRACTS DATA USING SQL FROM THE DATABASE AND STOREIT IN BINARY FILE
       • PHYSICAL BACKUPS EXTRACTS DATA FROM PHYSICAL STORAGE
   • DATABASE TRANSACTION RECOVERY
     • DATABASE CHECKPOINTS
     • DATABASE BUFFERS
     • REDUNDANT TRANSACTION LOGS
     • WRITE-AHEAD-LOG PROTOCOL
   • DATABASE RECOVERY - PROCESS OF RESTORING DATA

6. TRANSACTION PROCESSING SYSTEMS CATEGORY

   • REAL TIME TRANSACTION PROCESSING SYSTEM
     • D : THE IMMEDIATE PROCESSING OF DATA WITH THE DATABASE UPDATED AS THEM TRANSACTION IS BEING CARRIED
   • BATCH TRANSACTON PROCESSING
     • D : PROCESSING HAS THE COLLECTION OF DATA AT THE TIME OF THE EVENT WITH ACTUAL UPDATING OF THE DATABASE
     • A : CONTROL OVER TIME OF PROCESSING
     • X : ERRORS CORRECTED AFTER THE PROCESSING OF DATA
   • ON-LINE TRANSACTION PROCESSING SYSTEM (OLTP )
     • D : A CLASS OF SYSTEM THAT FACILITATE AND MANAGE TRANSACTION - ORIENTED APPLICATION, TYPICALLY FOR DATA ENTRY AND RETRIEBL TRANSACTION PROCESSING
     • A : IT PROVIDE FASTER AND MORE ACCURATE FORECAST FOR REVENUE AND EXPENSES
     • X : IT MAKES THE DATABASE MUCH MORE SUSCEPTIBLE TO INTRUDER AN HACKERS BECAUSE IT MAKES THE DATABASE AVAILABLE WORLDWIDE

7. CONCURRENCY CONTROL WITH LOCKING METHODS

   • LOCKING : IF ONE USER UPDATES THE DATA, ALL OTHER USERS ARE DENIED ACCESS UNTIL THE UPDATE IS COMPLETED (OR ABORTED)
     • DEADLOCKS
       • ONCE IT OCCURS, THE APPLICATION INVOLVED CANNOT RESOLVE THE PROBLEMS, THE DBMS HAS TO RECOGNISE IT AND BREAKS IT
       • ONLY ONE WAY TO BREAK A DEADLOCK : ABORT ONE OR MORE OF THE TRANSACTIONOR APPLICATIONS INVOLVED
     • TWO PHASE LOCKING TO ENSURE SERIALIZABILITY
       • A GROWING PHASE IN WHICH A TRANSACTION ACQUIRES ALL THE LOCKS IT NEEDS BUT DOES NOT RELEASESANY LOCK. ONCE IT ACQUIRES LOCKS, IT IS IN ITS LOCKED POINT.
       • A SHRINKING PHASE IN WHICH A TRANSACTION RELEASES ALL THE LOCKS AND CANNOT ACQUIRE ANY NEW LOCKS.
     • LOCK TYPES : PROCESSING A TRANSACTION INVOLVES READING AND WRITING DATA TO A DATABASE
       • BINARY LOCK
       • EXCLUSIVE LOCK : EXISTS WHEN CONCURRENT TRANSACTION ARE GRANTED READ ACCESS ON THE BASIS OF A COMMON LOCK
     • LOCK GRANULARITY : AN IMPORTANT CONSIDERATION IN IMPLEMENTING CONCURRENCY IS CHOOSING THE LOCKING LEVEL
       • TABLE LEVEL
       • PAGE LEVEL
       • ROW LEVEL
       • FIELD LEVEL

8. START TRANSACTION AND COMMIT STATEMENTS

   • START TRANSACTION
     • TO START A TRANSACTION,YOU USE THE START TRANSACTION STATEMENT.THE BEGIN OR BEGINWORK IS THE ALIASES OF THE START TRANSACTION
       • START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; COMMIT;
   • COMMIT COMMAND
     • THE COMMIT COMMAND IS THE TRANSACTIONAL COMMAND USED TO SAVE CHANGES INVOKED BY A TRANSACTION TO THE DATABASES