1 / 28

Concurrency and locking Pertemuan 8

Concurrency and locking Pertemuan 8. Matakuliah : T0413 Tahun : 2009. What is a Transaction?. Your Mom’s bank account. Your bank account. Balance = $200. Balance = $1000. Transfer $100 from your account to your Mom’s account: - Debit $100 from Savings account

jacob-miranda
Download Presentation

Concurrency and locking Pertemuan 8

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Concurrency and lockingPertemuan 8 Matakuliah : T0413 Tahun : 2009

  2. What is a Transaction? Your Mom’s bank account Your bank account Balance = $200 Balance = $1000 Transfer $100 from your account to your Mom’s account: - Debit $100 from Savings account - Credit $100 to Checking account

  3. Concurrency overview App A App B App C App D

  4. Locking overview App A App B App C App D

  5. Concurrency • DB2 was designed as a multi-user database • Access to data must be coordinated properly and transparently using a mechanism to ensure data integrity and consistency • Without some form of concurrency control, the following problems may be encountered • Lost update • Uncommitted read • Non-repeatable read • Phantom read

  6. Update Reservations Set P-name = 'Instruct' Where Flight = 512 and Seat = '7C' and P_name is NULL Update Reservations Set P-name = 'Manager' Where Flight = 512 and Seat = '7C' and P_Name is NULL ? ... ... Instruct 512 7C 512 7C Manager Lost Update Reservations Flight P-Name Seat 512 7C 512 7B ... ... ...

  7. Uncommitted Read Reservations Flight P_Name Seat 512 7C 512 7B ... ... Select seat From Reservations Where P-name is NULL Update Reservations Set P-name = 'Instruct' Where Flight = 512 and Seat = '7C' and P_Name is NULL 1 2 512 7C Instruct 3 4 Roll back Incorrect results set

  8. Non-repeatable Read SEAT ORIGIN FLIGHT NAME DESTINATION DALLAS DENVER 512 7B .... .... 814 8A SAN JOSE DENVER .... 134 1C SAN JOSE HONOLULU .... .... Book a flight from Dallas to Honolulu

  9. Phantom Read Reservations Flight P-name Seat 512 7B Susan Liu 512 7A ... ... Select seat From Reservations Where P-name is NULL Update Reservations Set P-name = 'NULL' Where Flight = 512 and Seat = '7A' and P-name = 'Susan Liu ' 1 2 512 7B Repeat 1 now seat 7A is available 3

  10. Locking • DB2 uses locking to maintain data integrity • Locks are acquired automatically as needed to support a transaction and are released when the transaction terminates (COMMIT/ROLLBACK) • Locks can be acquired on tables or rows • Two basic types of locks: • Share locks (S locks) – acquired when an application wants to read and prevent others from updating the same row • Exclusive locks (X locks) – acquired when an application updates, inserts, or deletes a row

  11. Isolation Levels • DB2 provides different levels of protection to isolate data • Uncommitted Read (UR) • Cursor Stability (CS) • Read Stability (RS) • Repeatable Read (RR) • Isolation level can be specified at many levels • Session (application). Defaults to CS • Connection • Statement • For embedded SQL, the level is set at bind time • For dynamic SQL, the level is set at run time

  12. Comparing isolation levels

  13. Isolation Levels – Uncommitted Read • Uncommitted Read is also known as DIRTY READ • Lowest level of isolation • Provides highest degree of concurrency • No row locks are obtained on read operations • unless other application attempts to drop or alter table • Update operations act as if using Cursor Stability • Possible Situations • Uncommitted Read • Non-repeatable Read • Phantom Read • Situations Prevented • Loss of Update

  14. Isolation Levels – Cursor Stability • Cursor Stability is the default isolation level • Minimal degree of locking • Locks the "current" row of a cursor • If the row is only read • the lock is held until a new row is fetched or the unit of work is terminated • If the row is updated • the lock is held until the unit of work is terminated • Possible Situations • Non-repeatable Read • Phantom Read • Prevented Situations • Loss of Update • Uncommitted Read

  15. Isolation Levels – Read Stability • Locks all the rows an application retrieves within a unit of work • For a given cursor, it locks all rows that qualify for the result set • Moderate degree of locking • Possible Situations • Phantom Read • Prevented Situations • Loss of Update • Uncommitted Read • Non-repeatable Read

  16. Isolation Levels – Repeatable Read • Highest isolation level, least concurrency • Same query issued by the application more than once in a unit of work will give the same result each time • High degree of locking • Locks held on all rows processed to build the result set • i.e. rows not necessarily in the final result set may be locked • No other application can update, delete, or insert a row that would affect the result set until the unit of work completes • Possible Situations • none • Prevented Situations • Loss of Update • Uncommitted Read • Non-repeatable Read • Phantom Read

  17. Comparison of Isolation Level Terminolgy

  18. Statement Level Isolation SELECT ... WITH {UR | CS | RS | RR} UR = Uncommitted Read CS = Cursor Stability RS = Read Stability RR = Repeatable Read • Example Scenario: • Application needs to get a "rough" count of how many rows are in table. Performance is of utmost importance. Cursor Stability isolation level is required with the exception of one SQL statement: SELECT COUNT(*) FROM tab1 WITH UR

  19. Lock Escalation

  20. Lock Escalation • When optimizer thinks it is better to have one lock on the entire table, rather than multiple row locks • Database configuration parameters that affect lock escalation: • LOCKLIST – the amount of memory (4k pages) to manage locks for all connected applications • Default is 50 * 4K pages on Windows • MAXLOCKS –Max percentage of the entire lock list that a single application can use up • Default is 22 percent

  21. Lock Escalation (continued) • If the default values are used, lock escalation occurs when a single application requires more than 44K of lock memory • If lock escalation, increase the value of LOCKLIST and MAXLOCKS • Avoid lock escalations: Performance bottleneck • Check the DB2 diagnostic log file (db2diag.log). The location is shown below. On Windows XP, 2003: • C:\Documents and Settings\All Users\Application Data\IBM\DB2\DB2COPY1\<instance name> On Windows Vista: • ProgramData\IBM\DB2\ On Linux/UNIX: • INSTHOME/sqllib/db2dump (INSTHOME is the home directory of the instance owner)

  22. Lock Escalation indication in the db2diag.log 2007-01-02-23.04.43.699000 Instance:DB2 Node:000 PID:984(db2syscs.exe) TID:1720 Appid:*LOCAL.DB2.011003030417 data_management sqldEscalateLocks Probe:1 Database:SAMPLE -- Start Table Lock Escalation. -- Lock Count, Target : 28, 14 7570 6461 7465 2065 6d70 6c6f 7965 6520 update employee 7365 7420 6669 7273 746e 6d65 3d27 6162 set firstnme='ab 6327 c' 2007-01-02-23.04.43.699001 Instance:DB2 Node:000 PID:984(db2syscs.exe) TID:1720 Appid:*LOCAL.DB2.011003030417 data_management sqldEscalateLocks Probe:2 Database:SAMPLE -- Lock Count, Target : 28, 14 -- Table (ID) Name : (2;5) ADMINISTRATOR.EMPLOYEE -- Locks, Request Type : 25, X -- Result (0 = success): 0

  23. Application Lock Snapshot Snapshot timestamp = 11-05-2002 00:09:08.672586 Application handle = 9 Application ID = *LOCAL.DB2.00B9C5050843 Sequence number = 0001 Application name = db2bp.exe Authorization ID = ADMINISTRATOR Application status = UOW Waiting Status change time = Not Collected Application code page = 1252 Locks held = 4 Total wait time (ms) = 0 List Of Locks Lock Name = 0x05000700048001000000000052 Lock Attributes = 0x00000000 Release Flags = 0x40000000 Lock Count = 255 Hold Count = 0 Lock Object Name = 98308 Object Type = Row Tablespace Name = TEST4K Table Schema = ADMINISTRATOR Table Name = T2 Mode = X Lock Snapshot • View locks currently held by • an application • UPDATE MONITOR • SWITCHES USING LOCK ON • GET SNAPSHOT FOR LOCKS • FOR APPLICATION • AGENT ID <handle>

  24. Lock Wait • By default, an application waits indefinitely to obtain any needed locks • LOCKTIMEOUT (db cfg): • Change to specify the number of seconds to wait for a lock • Default value is -1 or infinite wait • A database connection also has a user-definable CURRENT LOCK TIMEOUT register • By default it inherits its value from the LOCKTIMEOUT parameter • Use the SET LOCK TIMEOUT statement to change its value • Once it is set for a connection, it persists across transactions • e.g. SET LOCK TIMEOUT=WAIT n

  25. Deadlock Causes and Detection RAISIN BRAN MILK USER A USER B INSERT CEREAL AND MILK into BOWL within a UNIT OF WORK (UOW)

  26. Deadlock Settings • Deadlocks are an application design issue most of the time • DLCHKTIME(db cfg) sets the time interval for checking for deadlocks • It defines the frequency that the database manager checks for deadlocks among all the applications connected to a database • If you are experiencing many deadlocks, you should re-examine your existing transactions and see if any re-structuring is possible

  27. Best Practices • Keep transactions as short as possible • Issue frequent COMMIT statements – even for read-only transactions • Log transaction information only when required • Purge data quick using: • ALTER TABLE ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE • Perform data modifications in batches/groups • DELETE FROM ( SELECT * FROM tedwas.t1 WHERE c1 = … FETCH FIRST 3000 ROWS ONLY ) • Use concurrency features in DB2 data movement tools • Set the database level LOCKTIMEOUT parameter (usually between 30-120 seconds) • Can also use session-based lock timeout • Do not retrieve more data than is required • Use the FETCH FIRST n ROWS ONLY clause in SELECT statements

More Related