Understanding Record and Table Locking In OpenEdge ® SQL Applications Jeff Owen Principle Software Engineer, OESQL Progress Software Session 132

© 2009 Progress Software Corporation. All rights reserved. Goals We will be able to: Improve query performance Avoid lock table overflows Handle lock conflicts Improve concurrency

© 2009 Progress Software Corporation. All rights reserved. The Pieces Of The Lock Puzzle The OpenEdge SQL locking model Strategies that influence locking Tuning Queries Handling lock contention

© 2009 Progress Software Corporation. All rights reserved. Basics of Locking: Isolation Levels Isolation Level Read Uncommitted Read Committed Repeatable Read Serializable Number of Rows Locked One At A Time All Rows All That Satisfy Query Share Locks Table Lock No Locks

© 2009 Progress Software Corporation. All rights reserved. Isolation Level Affect on Lock Type Isolation LevelUpdate Table Record Lock Lock Fetch Table Record Lock Lock Read Uncommitted NoLock Read Committed IX Exclusive IS Share Repeatable Read IX Exclusive IS Share Serializable SIX Exclusive Share None

© 2009 Progress Software Corporation. All rights reserved. Where to monitor locks? PROMON Option 4: Record Locking Table

© 2009 Progress Software Corporation. All rights reserved. Monitoring Locks In OpenEdge Management

© 2009 Progress Software Corporation. All rights reserved. Basics of transactions Life Span of Locks Transaction Scope Transaction Begin Transaction Ends

© 2009 Progress Software Corporation. All rights reserved. Basics of Transactions Transaction Scope Start Statement.executeQuery(query_stmt); Statement.executeUpdate(update_stmt); Connection.prepareStatement(stmt);

© 2009 Progress Software Corporation. All rights reserved. Basics of Transactions Transaction Scope End Implicit: con.setAutoCommit(true);Explicit: con.commit(); OR con.rollback();

© 2009 Progress Software Corporation. All rights reserved. Basics of Transactions Change The Isolation Level To Change Lock Strength con.setTransactionIsolation( Connection.TRANSACTION_READ_COMMITTED);

© 2009 Progress Software Corporation. All rights reserved. ODBC – Setting Isolation Level

© 2009 Progress Software Corporation. All rights reserved. Locking Contention or Overflow : Reporting Strategies Help the Cost-Based Optimizer Select only the columns you need Use good predicates Possibly define more indexes ** UPDATE TABLE STATISTICS AND INDEX STATISTICS AND [ALL] COLUMN STATISTICS [FOR table_name];

© 2009 Progress Software Corporation. All rights reserved. Measuring and Tuning Strategies Query Plan Quickly Tune With NOEXECUTE SELECT SUBSTRING(_Description,1,80) FROM pub._Sql_Qplan SELECT name FROM pub.customer WHERE countryName != USA NOEXECUTE

© 2009 Progress Software Corporation. All rights reserved. Locking Contention: Utilizing A Lock Timeout OESQL SERVER Database Environment Variable: PRO_SQL_LOCK_TIMEOUT SQL Clients

© 2009 Progress Software Corporation. All rights reserved. Locking Contention: Read Retry Assumption: Isolation level = Read Committed Unable To Acquire Lock: Error CATCH The Lock Error Retry Fetching The Record

© 2009 Progress Software Corporation. All rights reserved. Locking Contention: Try To Read…….. // Execute query to find all customers rs1 = stmt.executeQuery ("SELECT name FROM pub.customer"); // fetch rows until all are read or until // a lock wait is too long while (read_rows) { try { if (rs1.next()) row_num++; else read_rows = false; // no more rows }

© 2009 Progress Software Corporation. All rights reserved. Locking Contention: Try To Read…….. // fetch rows until all are read or until // a lock wait is too long while (read_rows) { try { if (rs1.next()) row_num++; else read_rows = false; // no more rows } catch (SQLException ex) {

© 2009 Progress Software Corporation. All rights reserved. Locking Contention: Catch The Lock Conflict And Retry catch (SQLException e) { int error_code = e.getErrorCode(); // Throw the exception if row/table lock // not detected. Otherwise, retry. if (error_code != ) throw e; retries++; } // end catch if (retries == MAX_FETCH_RETRIES) read_rows = false; } // end while

© 2009 Progress Software Corporation. All rights reserved. Locking Contention: Catch The Lock Conflict And Retry catch (SQLException ex) { int error_code = ex.getErrorCode(); // Throw the exception if row/table lock // not detected. Otherwise, retry. if (error_code != ) throw ex; retries++; } // end catch if (retries == MAX_FETCH_RETRIES) read_rows = false; } // end while

© 2009 Progress Software Corporation. All rights reserved. Locking Contention: Catch The Lock Conflict And Retry catch (SQLException ex) { int error_code = ex.getErrorCode(); // Throw the exception if row/table lock // not detected. Otherwise, retry. if (error_code != ) throw ex; retries++; } // end catch if (retries == MAX_FETCH_RETRIES) read_rows = false ; } // end while

© 2009 Progress Software Corporation. All rights reserved. Locking Contention: Catch The Lock Conflict And Retry catch (SQLException ex) { int error_code = ex.getErrorCode(); // Throw the exception if row/table lock // not detected. Otherwise, retry. if (error_code != ) throw ex; retries++; } // end catch // only wait so long for the locked row if (retries == MAX_FETCH_RETRIES) read_rows = false; } // end while

© 2009 Progress Software Corporation. All rights reserved. Locking Contention: Locking Hint Provide A Locking Hint SELECT * FROM pub.Status WITH (NOLOCK) ;

© 2009 Progress Software Corporation. All rights reserved. Locking Contention or Overflow: Find NoLock, Re-find Using A Rowid Assumption: Isolation Level = Read Committed Fetch the ROWID using NOLOCK hint Fetch the record with the ROWID

© 2009 Progress Software Corporation. All rights reserved. Minimize Lock Contention: Get The Row Id NOLOCK // 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 = ?";

© 2009 Progress Software Corporation. All rights reserved. // 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)"; // Query for specific customer, lock one record String cust_addr_qry = "SELECT name FROM pub.customer " + "WHERE ROWID = ?"; Get The Row Id NOLOCK

© 2009 Progress Software Corporation. All rights reserved. Minimize Lock Contention: Fetch The Row With The Rowid // 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();

© 2009 Progress Software Corporation. All rights reserved. Minimize Lock Contention: Fetch The Row With The Rowid // 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();

© 2009 Progress Software Corporation. All rights reserved. Minimize Lock Contention: Fetch The Row With The Rowid // 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();

© 2009 Progress Software Corporation. All rights reserved. Finishing The Puzzle We can improve reporting performance by: Understanding Isolation Levels Utilizing Query Plans to tune query execution

© 2009 Progress Software Corporation. All rights reserved. Finishing The Puzzle We can manage lock contention in applications by: Increasing the Lock Wait Timeout Find No Lock Then Fetch Using A Rowid Detect A Lock Conflict and Retry The Record Read

© 2009 Progress Software Corporation. All rights reserved. For More Information, go to… PSDN OESQL Locking, Optimizer, Statistics White Papers Developing Performance-Oriented ODBC/JDBC OpenEdge Applications OpenEdge SQL: Authorization Explained Progress eLearning Community: Using OpenEdge SQL Documentation: 10.2A OpenEdge Data Management: SQL Development 10.2A OpenEdge Data Management: SQL Reference

Understanding Record and Table Locking In OpenEdge ® SQL Applications Jeff Owen Principle Software Engineer, OESQL Progress Software Session 132