Module 15: Managing Transactions and LocksOverviewIntroduction to Transactions and LocksManaging TransactionsPowerPoint PresentationTransaction Recovery and CheckpointsConsiderations for Using TransactionsSetting the Implicit Transactions OptionRestrictions on User-defined TransactionsSQL Server LockingConcurrency Problems Prevented by LocksLockable ResourcesTypes of LocksLock CompatibilityManaging LocksSession-Level Locking OptionsDynamic Locking ArchitectureTable-Level Locking OptionsDeadlocksDisplaying Locking InformationRecommended PracticesReviewModule 15: Managing Transactions and LocksOverviewIntroduction to Transactions and LocksManaging TransactionsSQL Server LockingManaging LocksIntroduction to Transactions and LocksTransactions Ensure That Multiple Data Modifications Are Processed TogetherLocks Prevent Update ConflictsTransactions are serializableLocking is automaticLocks allow concurrent use of dataConcurrency Control Managing TransactionsMultimedia Presentation: SQL Server TransactionsTransaction Recovery and CheckpointsConsiderations for Using TransactionsSetting the Implicit Transactions OptionRestrictions on User-defined TransactionsMultimedia Presentation: SQL Server TransactionsTransaction Recovery and CheckpointsTime (and place in log)DatabaseTransaction LogTransaction LogINSERT …DELETE …UPDATE ……INSERT …DELETE …UPDATE ……INSERT …DELETE …UPDATE ……INSERT …DELETE …UPDATE ……INSERT …DELETE …UPDATE ……CHECKPOINTCRASH!!!COMMITCOMMITCOMMITRecovery Needed? NONERecovery Needed? NONERecovery Needed? ROLL FORWARDRecovery Needed? ROLL FORWARDRecovery Needed? ROLL BACKRecovery Needed? ROLL BACKRecovery Needed? ROLL FORWARDRecovery Needed? ROLL FORWARDRecovery Needed? ROLL BACKRecovery Needed? ROLL BACKZOT!Considerations for Using TransactionsTransaction GuidelinesKeep transactions as small as possibleUse caution with certain Transact-SQL statementsAvoid transactions that require user interactionIssues in Nesting TransactionsAllowed, but not recommendedUse @@trancount to determine nesting levelSetting the Implicit Transactions OptionAutomatically Starts a Transaction When You Execute Certain StatementsNested Transactions Are Not AllowedTransaction Must Be Explicitly Completed with COMMIT or ROLLBACK TRANSACTIONBy Default, Setting Is OffSET IMPLICIT_TRANSACTIONS ONSET IMPLICIT_TRANSACTIONS ONALTER DATABASEBACKUP LOGCREATE DATABASEDROP DATABASERECONFIGURERESTORE DATABASERESTORE LOGUPDATE STATISTICSRestrictions on User-defined TransactionsCertain Statements May Not Be Included in a Transaction SQL Server LockingConcurrency Problems Prevented by LocksLockable ResourcesTypes of LocksLock CompatibilityConcurrency Problems Prevented by LocksLost UpdateUncommitted Dependency (Dirty Read)Inconsistent Analysis (Nonrepeatable Read)Phantoms ReadsLockable Resources ItemItem DescriptionDescriptionRID Row identifierKey Row lock within an indexPageExtentTableData page or index pageGroup of pagesEntire tableDatabase Entire databaseTypes of LocksBasic LocksSharedExclusiveSpecial Situation LocksIntentUpdateSchemaBulk updateLock CompatibilityLocks May or May Not Be Compatible with Other LocksExamplesShared locks are compatible with all locks except exclusiveExclusive locks are not compatible with any other locksUpdate locks are compatible only with shared locksSession-Level Locking OptionsDynamic Locking ArchitectureTable-Level Locking OptionsDeadlocksDisplaying Locking Information Managing LocksSession-Level Locking Options Transaction Isolation LevelREAD COMMITTED (DEFAULT)READ UNCOMMITTEDREPEATABLE READSERIALIZABLELocking TimeoutLimits time waiting for a locked resourceUse SET LOCK_TIMEOUTDynamic Locking ArchitectureDynamicLockingTablePageRowCostGranularityLocking CostConcurrency CostTable-Level Locking OptionsUse with CautionCan Specify One or More Locking Options for a TableUse optimizer_hints Portion of FROM Clause inSELECT or UPDATE StatementOverrides Session-Level Locking OptionsDeadlocksHow SQL Server Ends A DeadlockHow to Minimize DeadlocksHow to Customize the Lock Time-Out SettingDisplaying Locking InformationCurrent Activity Windowsp_lock System Stored ProcedureSQL ProfilerWindows 2000 System MonitorAdditional InformationRecommended PracticesDesign Transactions to Minimize DeadlocksDesign Transactions to Minimize DeadlocksUse SQL Server Defaults for LockingUse SQL Server Defaults for LockingKeep Transactions ShortKeep Transactions ShortBe Careful When You Use Locking OptionsBe Careful When You Use Locking OptionsReviewIntroduction to Transactions and LocksManaging TransactionsSQL Server LockingManaging