Tuning of Transactions(Cont.) Reducing lock contention Long transactions(typically read-only)that examine large parts of a relation result in lock contention with update transactions E.g.,large query to compute bank statistics and regular bank transactions To reduce contention Use multi-version concurrency control E.g.,Oracle "snapshots"which support multi-version 2PL Use degree-two consistency(cursor-stability)for long transactions Drawback:result may be approximate Database System Concepts-6th Edition 24.17 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 24.17 ©Silberschatz, Korth and Sudarshan th Edition Tuning of Transactions (Cont.) Reducing lock contention Long transactions (typically read-only) that examine large parts of a relation result in lock contention with update transactions E.g., large query to compute bank statistics and regular bank transactions To reduce contention Use multi-version concurrency control E.g., Oracle “snapshots” which support multi-version 2PL Use degree-two consistency (cursor-stability) for long transactions Drawback: result may be approximate
Tuning of Transactions(Cont.) Long update transactions cause several problems Exhaust lock space Exhaust log space and also greatly increase recovery time after a crash,and may even exhaust log space during recovery if recovery algorithm is badly designed! Use mini-batch transactions to limit number of updates that a single transaction can carry out.E.g.,if a single large transaction updates every record of a very large relation,log may grow too big. Split large transaction into batch of "mini-transactions,"each performing part of the updates Hold locks across transactions in a mini-batch to ensure serializability If lock table size is a problem can release locks,but at the cost of serializability In case of failure during a mini-batch,must complete its remaining portion on recovery,to ensure atomicity. Database System Concepts-6th Edition 24.18 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 24.18 ©Silberschatz, Korth and Sudarshan th Edition Tuning of Transactions (Cont.) Long update transactions cause several problems Exhaust lock space Exhaust log space and also greatly increase recovery time after a crash, and may even exhaust log space during recovery if recovery algorithm is badly designed! Use mini-batch transactions to limit number of updates that a single transaction can carry out. E.g., if a single large transaction updates every record of a very large relation, log may grow too big. * Split large transaction into batch of “mini-transactions,'' each performing part of the updates ⚫ Hold locks across transactions in a mini-batch to ensure serializability If lock table size is a problem can release locks, but at the cost of serializability * In case of failure during a mini-batch, must complete its remaining portion on recovery, to ensure atomicity