Transcription
Performance and Tuning Series:Locking and Concurrency ControlAdaptive Server Enterprise15.0.2
DOCUMENT ID: DC00938-01-1502-01LAST REVISED: October 2008Copyright 2008 by Sybase, Inc. All rights reserved.This publication pertains to Sybase software and to any subsequent release until otherwise indicated in new editions or technical notes.Information in this document is subject to change without notice. The software described herein is furnished under a license agreement,and it may be used or copied only in accordance with the terms of that agreement.To order additional documents, U.S. and Canadian customers should call Customer Fulfillment at (800) 685-8225, fax (617) 229-9845.Customers in other countries with a U.S. license agreement may contact Customer Fulfillment via the above fax number. All otherinternational customers should contact their Sybase subsidiary or local distributor. Upgrades are provided only at regularly scheduledsoftware release dates. No part of this publication may be reproduced, transmitted, or translated in any form or by any means, electronic,mechanical, manual, optical, or otherwise, without the prior written permission of Sybase, Inc.Sybase trademarks can be viewed at the Sybase trademarks page at http://www.sybase.com/detail?id 1011207. Sybase and the marks listedare trademarks of Sybase, Inc. indicates registration in the United States of America.Java and all Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the U.S. and other countries.Unicode and the Unicode Logo are registered trademarks of Unicode, Inc.All other company and product names mentioned may be trademarks of the respective companies with which they are associated.Use, duplication, or disclosure by the government is subject to the restrictions set forth in subparagraph (c)(1)(ii) of DFARS 52.227-7013for the DOD and as set forth in FAR 52.227-19(a)-(d) for civilian agencies.Sybase, Inc., One Sybase Drive, Dublin, CA 94568.
ContentsAbout This Book . viiCHAPTER 1Introduction to Locking . 1How locking affects performance . 1Locking and data consistency . 2Granularity of locks and locking schemes . 3Allpages locking . 4Datapages locking . 6Datarows locking . 7Types of locks in Adaptive Server . 7Page and row locks . 8Table locks . 10Demand locks. 13Row-locked system tables. 16Range locking for serializable reads . 16Latches. 17Lock compatibility and lock sufficiency. 18How isolation levels affect locking. 19Isolation level 0, read uncommitted . 20Isolation level 1, read committed . 21Isolation level 2, repeatable read . 23Isolation level 3, serializable reads. 23Adaptive Server default isolation level . 25Lock types and duration during query processing. 26Lock types during create index commands . 29Locking for select queries at isolation level 1 . 29Table scans and isolation levels 2 and 3. 30When update locks are not required . 30Locking during or processing . 31Skipping uncommitted inserts during selects . 32Using alternative predicates to skip nonqualifying rows . 33Pseudocolumn-level locking. 34Select queries that do not reference the updated column . 35Qualifying old and new values for uncommitted updates . 36Performance and Tuning Series: Locking and Concurrency Controliii
ContentsReducing contention . 37CHAPTER 2Locking Configuration and Tuning . 39Locking and performance. 39Using sp sysmon and sp object stats . 40Reducing lock contention . 40Additional locking guidelines . 43Configuring locks and lock promotion thresholds. 44Configuring the Adaptive Server lock limit. 44Configuring the lock hashtable (lock manager) . 46Setting lock promotion thresholds . 47Choosing the locking scheme for a table . 53Analyzing existing applications. 53Choosing a locking scheme based on contention statistics . 54Monitoring and managing tables after conversion. 56Applications not likely to benefit from data-only locking . 56Optimistic index locking. 57Using optimistic index locking . 58Cautions and issues . 58CHAPTER 3Locking Reports . 61Locking tools . 61Getting information about blocked processes . 61Viewing locks with sp lock . 63Viewing locks with sp familylock. 66Intrafamily blocking during network buffer merges. 66Deadlocks and concurrency . 67Server-side versus application-side deadlocks . 67Server task deadlocks . 67Deadlocks and parallel queries . 69Printing deadlock information to the error log. 70Avoiding deadlocks . 71Identifying tables where concurrency is a problem . 73Lock management reporting . 74CHAPTER 4Using Locking Commands . 77Specifying the locking scheme for a table. 77Specifying a server-wide locking scheme . 77Specifying a locking scheme with create table. 78Changing a locking scheme with alter table . 78Before and after changing locking schemes . 79Expense of switching to or from allpages locking. 80ivAdaptive Server Enterprise
ContentsSort performance during alter table.Specifying a locking scheme with select into .Controlling isolation levels.Setting isolation levels for a session .Syntax for query-level and table-level locking options .Using holdlock, noholdlock, or shared.Using the at isolation clause.Making locks more restrictive .Making locks less restrictive .Readpast locking.Cursors and locking .Using the shared keyword.Additional locking commands.lock table .Lock timeouts .CHAPTER 5818182828383848586868788898990Indexes. 91Types of indexes . 92Index pages. 93Index size . 94Indexes and partitions . 95Local indexes on partitioned tables . 95Global indexes on partitioned tables . 95Local versus global indexes . 96Unsupported partition index types. 96Clustered indexes on allpages-locked tables. 96Clustered indexes and select operations . 97Clustered indexes and insert operations . 98Page splitting on full data pages . 99Page splitting on index pages . 101Performance impacts of page splitting . 101Overflow pages . 102Clustered indexes and delete operations . 103Nonclustered indexes. 105Leaf pages revisited . 105Nonclustered index structure. 106Nonclustered indexes and select operations. 107Nonclustered index performance . 108Nonclustered indexes and insert operations . 109Nonclustered indexes and delete operations . 110Clustered indexes on data-only-locked tables. 111Index covering. 111Covering matching index scans . 112Covering nonmatching index scans . 113Performance and Tuning Series: Locking and Concurrency Controlv
Indexes and caching. 114Using separate caches for data and index pages. 115Index trips through the cache . 115CHAPTER 6Indexing for Concurrency Control. 117How indexes affect performance . 117Detecting indexing problems . 119Symptoms of poor indexing . 119Fixing corrupted indexes . 122Index limits and requirements. 125Choosing indexes . 125Index keys and logical keys . 127Guidelines for clustered indexes. 127Choosing clustered indexes. 128Candidates for nonclustered indexes . 128Choosing function-based indexes. 129Index selection. 129Other indexing guidelines . 132Choosing nonclustered indexes. 133Choosing composite indexes. 134Key order and performance in composite indexes . 134Advantages and disadvantages of composite indexes. 136Using online reorg rebuild for data-only-locked indexes. 137Techniques for choosing indexes . 137Choosing an index for a range query. 137Adding a point query with different indexing requirements . 138Index and statistics maintenance . 140Dropping indexes that hurt performance . 140Choosing space management properties for indexes . 140Additional indexing tips.
Performance and Tuning Series: Locking and Concurrency Control vii About This Book Audience This manual is intended for database administrators, database designers, developers and system administrators. How to use this book † Chapter 1, “Introduction to Locking” – describes the types of locks that Adaptive Server uses and what types of locks are acquired during
