1 / 44

OPS-27: Understanding Record and Table Locking In OpenEdge ® SQL Applications

OPS-27: Understanding Record and Table Locking In OpenEdge ® SQL Applications. Brian Werne. Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management. Goals. What if you could: Improve concurrency? Avoid a lock table overflow? Increase throughput?

tory
Download Presentation

OPS-27: Understanding Record and Table Locking In OpenEdge ® SQL Applications

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. OPS-27: Understanding Record and Table Locking In OpenEdge® SQL Applications Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management

  2. Goals What if you could: • Improve concurrency? • Avoid a lock table overflow? • Increase throughput? • Deal with potential lock contention errors? OPS-27: Record and Table Locking In OpenEdge SQL Apps

  3. Review of locking model and definitions • Available SQL features to influence locking • Examples on lock contention strategies Unlock the facts: Combination ? - ? - ? OPS-27: Record and Table Locking In OpenEdge SQL Apps

  4. Basics of Locking Grades of Locks • Schema lock • Table lock • Record lock OPS-27: Record and Table Locking In OpenEdge SQL Apps

  5. Basics of Locking Lock types eXclusive (X) Intent eXclusive (IX) Share Intent eXclusive (SIX) Share (S) Intent Share (IS) NoLock () – dirty read Lock Type Lock strength OPS-27: Record and Table Locking In OpenEdge SQL Apps

  6. X S S S SQ S XQ SQ SQ SQ SQ SQ How locking conflicts can occur The Lock Table (simplified) Active & Queued (Waiting) Lock Entries Hash Table OPS-27: Record and Table Locking In OpenEdge SQL Apps

  7. Where to monitor locks? PROMON Option 4: Record Locking Table OPS-27: Record and Table Locking In OpenEdge SQL Apps

  8. Where to monitor locks? OpenEdge Management Locks and Latches page or VST _LockReq OPS-27: Record and Table Locking In OpenEdge SQL Apps

  9. Unlock the facts: Combination ? - ? - ? • Review of locking model and definitions • Available SQL features to influence locking • Examples on lock contention strategies OPS-27: Record and Table Locking In OpenEdge SQL Apps

  10. Basics of transactions Locking protocols • Transaction scope - No locks allowed outside a SQL transaction • Isolation level - Implicit lock strength depends on SQL isolation level setting OPS-27: Record and Table Locking In OpenEdge SQL Apps

  11. Basics of Transactions Transaction scope • Auto-commit - true • con.setAutoCommit(true); • Explicit commit/rollback • con.commit(); OR • con.rollback(); OPS-27: Record and Table Locking In OpenEdge SQL Apps

  12. Isolation Level Affect on Lock Type OPS-27: Record and Table Locking In OpenEdge SQL Apps

  13. Basics of Transactions The Basics • Setting Isolation level to affect “transaction strength” con.setTransactionIsolation( Connection.TRANSACTION_READ_COMMITTED); OPS-27: Record and Table Locking In OpenEdge SQL Apps

  14. ODBC – Setting Isolation Level OPS-27: Record and Table Locking In OpenEdge SQL Apps

  15. Strategies Beyond The Basics – increasing throughput • Lock Table statement: - Similar to Serializable ( High throughput, low concurrency) LOCK TABLE table_name [ , table_name ] , ... IN { SHARE | EXCLUSIVE } MODE ; OPS-27: Record and Table Locking In OpenEdge SQL Apps

  16. Strategies Beyond The Basics – increasing throughput • Select for Update: Similar to Repeatable Read ( High throughput, ‘better’ concurrency) Select id, name from PUB.Standings where balance < 100 FOR UPDATE; OPS-27: Record and Table Locking In OpenEdge SQL Apps

  17. Locking Contention or Overflow Avoiding lock table overflow error? OPS-27: Record and Table Locking In OpenEdge SQL Apps

  18. Strategies The basics of reporting • Helping the cost-based optimizer in the OpenEdge SQL engine UPDATE TABLE STATISTICS AND INDEX STATISTICS AND [ALL] COLUMN STATISTICS [FOR table_name]; • Selecting only the columns you need • Using good predicates • Possibly defining more indexes ** OPS-27: Record and Table Locking In OpenEdge SQL Apps

  19. Strategies Measuring and tuning the Basics • Query Plan select substring(“_Description”,1,80) from pub.”_Sql_Qplan”… • Using NoExecute to help query tuning Select name from pub.customer where countryName != ‘USA’ NOEXECUTE OPS-27: Record and Table Locking In OpenEdge SQL Apps

  20. Beyond the Basics Avoid lock table overflow or buffer pool flush • Limit result set size – Select TOP n SELECT TOP 5 Name, Balance FROM pub.Standings ORDER BY Balance; OPS-27: Record and Table Locking In OpenEdge SQL Apps

  21. Examples on lock contention strategies Unlock the facts: Combination ? - ? - ? • Review of locking model and definitions • Available SQL features to influence locking OPS-27: Record and Table Locking In OpenEdge SQL Apps

  22. Locking Contention Still getting lock contention errors? OPS-27: Record and Table Locking In OpenEdge SQL Apps

  23. Beyond the Basics Controlling Lock Timeout • ABL • Default for ABL = 30 minutes • Control process with ‘-lkwtmo’ • SQL • Default for SQL = 5 seconds • Process control is environment variable PROSQL_LOCKWAIT_TIMEOUT OPS-27: Record and Table Locking In OpenEdge SQL Apps

  24. Locking Contention – Read Retry Isolation level = Read Committed • Encounter lock error: -210015 • Try-catch block to capture lock error • Retry fetching the record OPS-27: Record and Table Locking In OpenEdge SQL Apps

  25. Locking Contention – Read Retry RECORD_LOCKED error - Read Retry rs1 = stmt.executeQuery("SELECT name FROM pub.customer"); while (read_rows) { try { if (rs1.next()) row_num++; else read_rows = false; // no more rows } catch (SQLException e) { OPS-27: Record and Table Locking In OpenEdge SQL Apps

  26. Locking Contention – Read Retry RECORD_LOCKED error - Read Retry (part2) catch (SQLException e) { int error_code = e.getErrorCode(); // Throw the exception if row/table lock // not detected. Otherwise, retry. if (error_code != -210015) throw e; retries++; } // end catch if (retries == MAX_FETCH_RETRIES) read_rows = false; } // end while OPS-27: Record and Table Locking In OpenEdge SQL Apps

  27. Locking Contention • Make use of locking hints: SELECT * from PUB.Status WITH (…) ; OPS-27: Record and Table Locking In OpenEdge SQL Apps

  28. Locking Contention – Readpast Isolation level = Read Committed • Set lock wait timeout value with READPAST locking hint • Action: Skips record if timeout value exceeded **Warning**: possible to get incomplete resultset OPS-27: Record and Table Locking In OpenEdge SQL Apps

  29. Locking Contention - Readpast Using READPAST Nowait locking hint // Skip lock conflict error and the associated row String select1 = "SELECT name FROM pub.customer" + " WITH (READPAST NOWAIT)"; // record read will return immediately if // there is a lock conflict rs1 = stmt.executeQuery(select1); while (rs1.next()) { System.out.println("Customer Name: " + rs1.getString(1)); } OPS-27: Record and Table Locking In OpenEdge SQL Apps

  30. Locking Contention - Readpast Using READPAST WAIT n String select2 = "SELECT name FROM pub.customer" + " WITH (READPAST WAIT 10)"; // record read will wait for up to 10 seconds if // there is a lock conflict rs2 = stmt.executeQuery(select2); while (rs2.next()) { System.out.println("Customer Name: " + rs2.getString(1)); } OPS-27: Record and Table Locking In OpenEdge SQL Apps

  31. Locking Contention – NoLock and Rowid Isolation level = Read Committed • Fetch initial record using NoLock locking hint • Using ROWID: re-fetch record later with appropriate lock OPS-27: Record and Table Locking In OpenEdge SQL Apps

  32. Locking Contention – NoLock and Rowid Using NoLock locking hint // Get ROWID for customer at Hurricane Lane without // locking record String cust_addr = "Hurricane Lane"; String cust_addr_qry_nl = "SELECT ROWID FROM pub.customer WHERE address = \'" + cust_addr + "\' WITH (NOLOCK)"; // Get specific customer and only lock one record String cust_addr_qry = "SELECT name FROM pub.customer " + "WHERE ROWID = ?"; OPS-27: Record and Table Locking In OpenEdge SQL Apps

  33. Locking Contention – NoLock and Rowid Using NoLock lock hint (part 2) // Execute query to get rowid for a customer without // any record locks rs1 = stmt.executeQuery(cust_addr_qry_nl); // Using the result set from the FIND NOLOCK query, // use the rowid as a parameter to the query // that will lock only one record while (rs1.next()) { rowidInt = rs1.getInt(1); pstmt.setInt(1, rowidInt); rs2 = pstmt.executeQuery(); … OPS-27: Record and Table Locking In OpenEdge SQL Apps

  34. - 4 6 - 5 • Definitions • SQL features that affect locking • Examples on handling lock contention In Summary : Combination ? - ? - ? OPS-27: Record and Table Locking In OpenEdge SQL Apps

  35. In Summary : Unlocked - 4 6 - 5 OPS-27: Record and Table Locking In OpenEdge SQL Apps

  36. For More Information, go to… • PSDN • Locking talks – many Exchanges • Developing Performance-Oriented ODBC/JDBC OpenEdge Applications • OpenEdge SQL: Authorization Explained • Progress eLearning Community: • Using OpenEdge SQL • Documentation: • 10.1C OpenEdge Data Management: SQL Development    • 10.1C OpenEdge Data Management: SQL Reference OPS-27: Record and Table Locking In OpenEdge SQL Apps

  37. ? Questions OPS-27: Record and Table Locking In OpenEdge SQL Apps

  38. Thank You OPS-27: Record and Table Locking In OpenEdge SQL Apps

  39. OPS-27: Record and Table Locking In OpenEdge SQL Apps

  40. Row Locks * NO-LOCK means “access without lock” OPS-27: Record and Table Locking In OpenEdge SQL Apps

  41. S X UpgX Other Active Locks S OK - Q - - Q - X - Q - - Q - - Q - QS/QE - Q - - Q - - Q - Row Lock Compatibility Lock Request Type OPS-27: Record and Table Locking In OpenEdge SQL Apps

  42. Table Lock Types OPS-27: Record and Table Locking In OpenEdge SQL Apps

  43. Table Lock Compatibility Lock Request Type Other Active Locks OPS-27: Record and Table Locking In OpenEdge SQL Apps

  44. Basics of transactions - ACID • Atomicity • Consistency • Isolation • Durability OPS-27: Record and Table Locking In OpenEdge SQL Apps

More Related