savin1资料db2课程2 lock concurrency

1、Lock & ConcurrencyGrow market shareThrive for excellenceSucceed with smarter solutionsIBM Confidential 2009 IBM Corporation9 slidesGIBloMbIanlfoTremcahtnioonloMgaynSaegrevmiceenstWhy are locks needed?Page 2Page 2PI+E |IBM DB2 AdministrationIBM Confidential 2009 IBM CorporationGlobal Technology Servi

2、cesGrowth ? Thrive for excellence ? Smarter solutionsClassification of locksPage 3Page 3PI+E |IBM DB2 AdministrationIBM Confidential 2009 IBM CorporationGlobal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsObjects of locksPage 4Page 4PI+E |IBM DB2 AdministrationIBM Confidentia

3、l 2009 IBM CorporationGlobal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsTable lock modesWSE在linux上最多使用4CPU/16G内存Page 5Page 5PI+E |IBM DB2 AdministrationIBM Confidential 2009 IBM CorporationGlobal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsRow lock

4、modesWSE在linux上最多使用4CPU/16G内存Page 6Page 6PI+E |IBM DB2 AdministrationIBM Confidential 2009 IBM CorporationGlobal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsWhen two application meetsWSE在linux上最多使用4CPU/16G内存Page 7Page 7PI+E |IBM DB2 AdministrationIBM Confidential 2009 IBM Co

5、rporationGlobal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsIntroduction to isolation levelsPage 8Page 8PI+E |IBM DB2 AdministrationIBM Confidential 2009 IBM CorporationGlobal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsLock wait and timeoutPage 9Pag

6、e 9PI+E |IBM DB2 AdministrationIBM Confidential 2009 IBM CorporationGlobal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsLock escalationWSE在linux上最多使用4CPU/16G内存Page 10Page1P0I+E|IBM DB2 AdministrationIBM Confidential 2009 IBM CorporationGlobal Technology ServicesGrowth ? Thriv

7、e for excellence ? Smarter solutionsSET CURRENT LOCK TIMEOUTPage 11Page1P1I+E|IBM DB2 AdministrationIBM Confidential 2009 IBM CorporationGlobal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsDeadlockPage 12Page1P2I+E|IBM DB2 AdministrationIBM Confidential 2009 IBM CorporationGl

8、obal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsSupport Other Vendors Concurrency Modelsn Easy for developers to work with DB2n No need to rewrite concurrent access logic Optimize throughput for mixed workloads Eliminate conflicts between Read workloads and Write workloadsn

9、 Log-based control No management overhead13Page 13IBM DB2 AdministrationIBM Confidential 2009 IBM CorporationGlobal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsConcurrency Control Prior to DB2 9.7n Oracle default:Snapshot Isolationn DB2 default:Cursor stabilitysignificant ef

10、fort to re-order table14Page 14acceIBsM sDB2 tAodminaistrvatioon idIBM dConefidaentdial loc k200s9 IBM CorporationPorting other RDBMS applications to DB2 requiredSituationResultReader blocks ReaderNoReader blocks WriterMaybeWriter blocks ReaderYesWriter blocks WriterYesSituationResultReader blocks R

11、eaderNoReader blocks WriterNoWriter blocks ReaderNoWriter blocks WriterYesGlobal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsConcurrency Control in DB2 V9.7n Up to DB2 9.5 Cursor Stability is the default isolation leveln Now in DB2 Cobra Currently Committed is the default fo

12、r NEW databases Currently Committed is disabled for upgraded databases, i.e., Cursor Stability semantics are usedn Applications that depend on the old behavior (writers blocking readers) will need to update their logic or disable the Currently Committed semantics.15Page 15IBM DB2 AdministrationIBM C

13、onfidential 2009 IBM CorporationGlobal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsConcurrency Control in DB2 V9.7n Currently Committed is a variation on DB2s Cursor Stability isolation If uncommitted row-change found, use currently committed version of data Avoids timeouts

14、and deadlocks Log based:No management overheadCursor StabilityCurrently Committed16Page 16IBM DB2 AdministrationIBM Confidential 2009 IBM CorporationSituationResultReader blocks ReaderNoReader blocks WriterNoWriter blocks ReaderYesWriter blocks WriterYesSituationResultReader blocks ReaderNoReader bl

15、ocks WriterNoWriter blocks ReaderNoWriter blocks WriterYesGlobal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsIsolation Levels how to specify in DB2n Statement Level Use the WITH clause Overrides the isolation level specified for the packagen Dynamic SQL within the current se

16、ssion SET CURRENT ISOLATION for all subsequent dynamic SQL statements within the same sessionn Precompile / Bind ISOLATION option of PREP or BIND commandn JDBC or SQLJ at run time SQLJ profile customizer (db2sqljcustomize command)n CLI or ODBC at run time CHANGE ISOLATION LEVEL command17Page 17IBM D

17、B2 AdministrationIBM Confidential 2009 IBM CorporationGlobal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsExample Cursor Stability (v9.5)update T1 set col1 = ? where col2 = 2update T2 set col1 = ? where col2 = ?select * from T2 where col2 = ?select * from T1 where col5 = ? an

18、d col2 = ?Waiting because isWaiting because isreading uncommitted datareading uncommitted data18Page 18IBM DB2 AdministrationIBM Confidential 2009 IBM CorporationDEADLOCK!Transaction ATransaction BGlobal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsExample Cursor Stability wi

19、th Currently Committedupdate T1 set col1 = ? where col2 = 2update T2 set col1 = ? where col2 = ?select * from T2 where col2 = ?select * from T1 where col5 = ? and col2 = ?commitcommitNo lockingReads last committed versionNo lockingReads last committed versionof the df the data19Page 19IBM DB2 Admini

20、strationIBM Confidential 2009 IBM CorporationtNa o deadlocks, no timeouts in this scenario!oTransaction ATransaction BGlobal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsCurrently Committed how to use itn cur_commit DB config parameter ON: default for new DBs created in Cobra

21、 CC semantics in place DISABLED: default value for existing DBs old CS semantics in placen PRECOMPILE/BIND CONCURRENTACCESSRESOLUTION: Specifies the concurrent access resolution to use for statements in the package. USE CURRENTLY COMMITTED WAIT FOR OUTCOME20Page 20IBM DB2 AdministrationIBM Confident

22、ial 2009 IBM CorporationGlobal Technology ServicesGrowth ? Thrive for excellence ? Smarter solutionsCurrently Committed Isolation : How it WorksLMPActive Log FilesDEL: Emp,5,5456,Baum,D2/18 INS: Emp,1,4245,Chan,Y2/11,11Log ArchiveINS:Emp,1,6354,Smith,A1/21, 43INS:Emp,4,1325,Tata,X1/03,33Uncommitted

23、INSERTed data is skipped.For uncommitted DELETEs and UPDATEs, when encountering a lock which would otherwise conflict, DB2 uses new information in the lock manager to reconstruct and return the previously committed data from the log buffer or log file.21Page 21IBM DB2 AdministrationIBM Confidential

24、2009 IBM Corporationrowidempidnameofficesalary486354SmithA1/2143774245ChanY2/1111967836JonesAA/00 C3/46211041325TataX1/0333 2055456BaumD2/1822Log BufferUPD: Emp,3,7836,Chan,AA/00?C3/46ocklistrowidlog ref7796205-lock X(I)X(U)X(D)Global Technology ServicesGrowth ? Thrive for excellence ? Smarter solut

25、ionsCurrently Committed : Internals & Usage Notesn Log-based implementation : simple & fast No need for rollback segments! Currently committed data typically reconstructed from memory (log buffer) Exception: updates/deletes from mass update transactions that spill log buffer Active logs read from st

26、orage in this casen Fallback to traditional locking If the currently committed data is unavailable (or not available quickly), DB2 will fall back to the traditional locking behavior Examples Currently committed data is only available from an archived log (as may be the case with infinite logging) Updater held table lock (not row lock)22Page 22IB