1 / 43

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

OPS-27: Record and Table Locking In OpenEdge SQL Apps. 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. Unlock the facts: Combination ? - ? - ?.

ezra
Télécharger la présentation

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 Welcome to Operations track. Understanding Locking in SQL applications. The last presentation slot at Exchange. My name is Brian Werne. Engineering manager for OpenEdge SQL and OEM. Before we start: who is using SQL for reporting? for transaction processing? Welcome to Operations track. Understanding Locking in SQL applications. The last presentation slot at Exchange. My name is Brian Werne. Engineering manager for OpenEdge SQL and OEM. Before we start: who is using SQL for reporting? for transaction processing?

    2. OPS-27: Record and Table Locking In OpenEdge SQL Apps Goals What if you could: Improve concurrency? Avoid a lock table overflow? Increase throughput? Deal with potential lock contention errors? Presentations of exchange past! What does this cover? What doesn’t it cover? Presentations of exchange past! What does this cover? What doesn’t it cover?

    3. OPS-27: Record and Table Locking In OpenEdge SQL Apps Unlock the facts: Combination ? - ? - ?

    4. OPS-27: Record and Table Locking In OpenEdge SQL Apps Basics of Locking Schema lock Table lock Record lock schema lock - using this name for the online schema locking protocol also - EXCL (offline), SHR (no schema updaters), online schema locking protocol Same as ABL Schema ( big lock) controls access to data definitions ( tables, indexes) - online schema lock, improves concurrency, Taken out during create table, add column, add inactive index, etc This is really a locking protocol, which is the second part of locking besides the locks themselves. Big schema lock , Drop table, drop index Table lock – usually managed by the storage engine ( Table in appendix of this presentation that shows when table locks are taken out on your behalf.) Record lock, sometimes referred to as a row lock as well. Schema - access to data definition Row/record - access to rows in table Table - access to all or part of table schema lock - using this name for the online schema locking protocol also - EXCL (offline), SHR (no schema updaters), online schema locking protocol Same as ABL Schema ( big lock) controls access to data definitions ( tables, indexes) - online schema lock, improves concurrency, Taken out during create table, add column, add inactive index, etc This is really a locking protocol, which is the second part of locking besides the locks themselves. Big schema lock , Drop table, drop index Table lock – usually managed by the storage engine ( Table in appendix of this presentation that shows when table locks are taken out on your behalf.) Record lock, sometimes referred to as a row lock as well. Schema - access to data definition Row/record - access to rows in table Table - access to all or part of table

    5. OPS-27: Record and Table Locking In OpenEdge SQL Apps Basics of Locking eXclusive (X) Intent eXclusive (IX) Share Intent eXclusive (SIX) Share (S) Intent Share (IS) NoLock () – dirty read One of 6 lock types: X (exclusive lock), S (share lock), IX (intent exclusive lock), IS (intent share lock), or SIX (shared lock on table with intent to set exclusive locks on records). ( Nolock lock) (parenthesis are what you would see in analysis tools like promon, VST, or promon) One of 6 lock types: X (exclusive lock), S (share lock), IX (intent exclusive lock), IS (intent share lock), or SIX (shared lock on table with intent to set exclusive locks on records). ( Nolock lock) (parenthesis are what you would see in analysis tools like promon, VST, or promon)

    6. OPS-27: Record and Table Locking In OpenEdge SQL Apps How locking conflicts can occur If you are only user connected to database, this is not relevent. However, in a real multi-user system, with multiple applications accessing the data. A tomicity C onsisency I solation D urability Isolation Main article: Isolation (computer science) Isolation refers to the ability of the application to make operations in a transaction appear isolated from all other operations. This means that no operation outside the transaction can ever see the data in an intermediate state; for example, a bank manager can see the transferred funds on one account or the other, but never on both — even if he ran his query while the transfer was still being processed. More formally, isolation means the transaction history (or schedule) is serializable. This ability is the constraint which is most frequently relaxed for performance reasons[citation needed]. IsolationIf you are only user connected to database, this is not relevent. However, in a real multi-user system, with multiple applications accessing the data. A tomicityC onsisency I solation D urability Isolation Main article: Isolation (computer science) Isolation refers to the ability of the application to make operations in a transaction appear isolated from all other operations. This means that no operation outside the transaction can ever see the data in an intermediate state; for example, a bank manager can see the transferred funds on one account or the other, but never on both — even if he ran his query while the transfer was still being processed. More formally, isolation means the transaction history (or schedule) is serializable. This ability is the constraint which is most frequently relaxed for performance reasons[citation needed]. Isolation

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

    8. OPS-27: Record and Table Locking In OpenEdge SQL Apps Where to monitor locks? VST, or OEM * slide 7, 8 monitoring locks o screens show different things. OE Mgmt can show who has the lock, same as promon does, using the VST _LockReq Transition coming*** VST, or OEM * slide 7, 8 monitoring locks o screens show different things. OE Mgmt can show who has the lock, same as promon does, using the VST _LockReq Transition coming***

    9. OPS-27: Record and Table Locking In OpenEdge SQL Apps Unlock the facts: Combination ? - ? - ?

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

    11. OPS-27: Record and Table Locking In OpenEdge SQL Apps Basics of Transactions Auto-commit - true Show how to do this with JDBC code Explain how different than ABL Talks bout how to do this with ODBC – refer to other talk Show how to do this with JDBC code Explain how different than ABL Talks bout how to do this with ODBC – refer to other talk

    12. OPS-27: Record and Table Locking In OpenEdge SQL Apps Isolation Level Affect on Lock Type Go through behavior of each isolation level Need to talk about dirty reads somewhere Go through behavior of each isolation level Need to talk about dirty reads somewhere

    13. OPS-27: Record and Table Locking In OpenEdge SQL Apps Basics of Transactions Setting Isolation level to affect “transaction strength” Transaction scope Keeping your transaction small and using the appropriate isolation level Transaction scope Keeping your transaction small and using the appropriate isolation level

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

    15. OPS-27: Record and Table Locking In OpenEdge SQL Apps Strategies Lock Table statement: - Similar to Serializable ( High throughput, low concurrency) Concurrency vs throughput Are sometimes in comflict, Transaction scope Keeping your transaction small and using the appropriate isolation level Test results database example Concurrency vs throughput Are sometimes in comflict, Transaction scope Keeping your transaction small and using the appropriate isolation level Test results database example

    16. OPS-27: Record and Table Locking In OpenEdge SQL Apps Strategies Select for Update: Similar to Repeatable Read ( High throughput, ‘better’ concurrency) Concurrency vs throughput Same as ABL locking Concurrency vs throughput Same as ABL locking

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

    18. OPS-27: Record and Table Locking In OpenEdge SQL Apps Strategies Helping the cost-based optimizer in the OpenEdge SQL engine Help the optimizer Run Update stats, helps avoid table scans, appropriate index selection, correct join order, All of these mean that less records will be read and hence locked John Goodson did a whole talk on this last year, I reference this in the notes in the later in this presentation. ** ( May have affect on OLTP throughput) Help the optimizer Run Update stats, helps avoid table scans, appropriate index selection, correct join order, All of these mean that less records will be read and hence locked John Goodson did a whole talk on this last year, I reference this in the notes in the later in this presentation. ** ( May have affect on OLTP throughput)

    19. OPS-27: Record and Table Locking In OpenEdge SQL Apps Strategies Query Plan Transaction scope Keeping your transaction small and using the appropriate isolation level select substring("_Description",1,80) from pub."_Sql_Qplan“ where "_Pnumber" = (select max("_Pnumber") from pub."_Sql_Qplan" where "_Ptype" > 0 ); Help the optimizer Run Update stats, helps avoid table scns, appropriate index sleection, correct join order, All of these mean that less records will be read and hence locked Transaction scope Keeping your transaction small and using the appropriate isolation level select substring("_Description",1,80) from pub."_Sql_Qplan“where "_Pnumber" = (select max("_Pnumber") from pub."_Sql_Qplan" where "_Ptype" > 0 ); Help the optimizer Run Update stats, helps avoid table scns, appropriate index sleection, correct join order, All of these mean that less records will be read and hence locked

    20. OPS-27: Record and Table Locking In OpenEdge SQL Apps Beyond the Basics Limit result set size – Select TOP n ** True value with indexed order by or group by Transition coming You’ve done the basics But you still need to either increase throughput or deal with lock contention erros ** True value with indexed order by or group by Transition coming You’ve done the basics But you still need to either increase throughput or deal with lock contention erros

    21. OPS-27: Record and Table Locking In OpenEdge SQL Apps Unlock the facts: Combination ? - ? - ?

    22. OPS-27: Record and Table Locking In OpenEdge SQL Apps Locking Contention Still getting lock contention errors? You’ve done it all and you are still getting lock contention errors. You’ve done it all and you are still getting lock contention errors.

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

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

    25. OPS-27: Record and Table Locking In OpenEdge SQL Apps Locking Contention – Read Retry import java.sql.*; public class retryRead { public static final int OE_ERR_RECORD_LOCKED = -210015; public static final int OE_ERR_TABLE_LOCKED = -210005; public static final int MAX_FETCH_RETRIES = 5; /** * */ public retryRead(String[] args) { String driverName = "com.ddtek.jdbc.openedge.OpenEdgeDriver"; String url = "jdbc:datadirect:openedge://" + args[0] + ":" + args[1] + ";databaseName=" + args[2]; String userName = args[3]; String password = args[4]; ResultSet rs1 = null; try { String query = "SELECT name FROM pub.customer"; System.out.println("retryRead entered"); Class.forName(driverName); Connection con = DriverManager.getConnection(url, userName, password); boolean retry = true; boolean read_rows = true; int retries = 0; int row_num = 0; con.setAutoCommit(false); // create a statement with a scrollable result set that can not be updated // since we are just fetching rows Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); try { rs1 = stmt.executeQuery(query); // fetch a row at a time for proper granularity for a retry effort rs1.setFetchSize(1); while (read_rows) { try { // read the next row if (rs1.next()) { System.out.println("Customer Name: " + rs1.getString(1)); row_num++; } else read_rows = false; // no more rows } catch (SQLException e) { int error_code = e.getErrorCode(); // Throw the execption higher if a row or table lock error is not // detected. Otherwise, continue to try and read a row until // the "timeout" value is reached if ((error_code != OE_ERR_RECORD_LOCKED) && (error_code != OE_ERR_TABLE_LOCKED)) { throw e; } retries++; } if (retries == MAX_FETCH_RETRIES) read_rows = false; } if (retries == MAX_FETCH_RETRIES) System.out.println("ABORT - Unable to read record"); } catch (SQLException e) { System.out.println("Statement failed"); showException(e); } System.out.println("TOTAL ROWS = " + row_num); rs1.close(); // close the cursor for the stmt stmt.close(); // drop the statement con.close(); } catch (SQLException e) { showException(e); } catch (Exception e) { String theReason = "OE_Test: exception thrown"; SQLException error = new SQLException(theReason); showException(error); } finally { System.out.println("\nretryRead exits"); } } private void showException(SQLException theError) { System.err.println("\n"); System.err.println("SQLClient Error: "); System.err.println("SQL State: " + theError.getSQLState()); System.err.println("Vender Code: " + theError.getErrorCode()); System.err.println("Reason: " + theError.getMessage()); } public static void main(String[] args) { new retryRead(args); } } import java.sql.*; public class retryRead { public static final int OE_ERR_RECORD_LOCKED = -210015; public static final int OE_ERR_TABLE_LOCKED = -210005; public static final int MAX_FETCH_RETRIES = 5; /** * */ public retryRead(String[] args) { String driverName = "com.ddtek.jdbc.openedge.OpenEdgeDriver"; String url = "jdbc:datadirect:openedge://" + args[0] + ":" + args[1] + ";databaseName=" + args[2]; String userName = args[3]; String password = args[4]; ResultSet rs1 = null; try { String query = "SELECT name FROM pub.customer"; System.out.println("retryRead entered"); Class.forName(driverName); Connection con = DriverManager.getConnection(url, userName, password); boolean retry = true; boolean read_rows = true; int retries = 0; int row_num = 0; con.setAutoCommit(false); // create a statement with a scrollable result set that can not be updated // since we are just fetching rows Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); try { rs1 = stmt.executeQuery(query); // fetch a row at a time for proper granularity for a retry effort rs1.setFetchSize(1); while (read_rows) { try { // read the next row if (rs1.next()) { System.out.println("Customer Name: " + rs1.getString(1)); row_num++; } else read_rows = false; // no more rows } catch (SQLException e) { int error_code = e.getErrorCode(); // Throw the execption higher if a row or table lock error is not // detected. Otherwise, continue to try and read a row until // the "timeout" value is reached if ((error_code != OE_ERR_RECORD_LOCKED) && (error_code != OE_ERR_TABLE_LOCKED)) { throw e; } retries++; } if (retries == MAX_FETCH_RETRIES) read_rows = false; } if (retries == MAX_FETCH_RETRIES) System.out.println("ABORT - Unable to read record"); } catch (SQLException e) { System.out.println("Statement failed"); showException(e); } System.out.println("TOTAL ROWS = " + row_num); rs1.close(); // close the cursor for the stmt stmt.close(); // drop the statement con.close(); } catch (SQLException e) { showException(e); } catch (Exception e) { String theReason = "OE_Test: exception thrown"; SQLException error = new SQLException(theReason); showException(error); } finally { System.out.println("\nretryRead exits"); } } private void showException(SQLException theError) { System.err.println("\n"); System.err.println("SQLClient Error: "); System.err.println("SQL State: " + theError.getSQLState()); System.err.println("Vender Code: " + theError.getErrorCode()); System.err.println("Reason: " + theError.getMessage()); } public static void main(String[] args) { new retryRead(args); } }

    26. OPS-27: Record and Table Locking In OpenEdge SQL Apps Locking Contention – Read Retry

    27. OPS-27: Record and Table Locking In OpenEdge SQL Apps Locking Contention Make use of locking hints: You’ve done it all and you are still getting lock contention errors. You’ve done it all and you are still getting lock contention errors.

    28. OPS-27: Record and Table Locking In OpenEdge SQL Apps 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 import java.sql.*; public class retryRead { public static final int OE_ERR_RECORD_LOCKED = -210015; public static final int OE_ERR_TABLE_LOCKED = -210005; public static final int MAX_FETCH_RETRIES = 5; /** * */ public retryRead(String[] args) { String driverName = "com.ddtek.jdbc.openedge.OpenEdgeDriver"; String url = "jdbc:datadirect:openedge://" + args[0] + ":" + args[1] + ";databaseName=" + args[2]; String userName = args[3]; String password = args[4]; ResultSet rs1 = null; try { String query = "SELECT name FROM pub.customer"; System.out.println("retryRead entered"); Class.forName(driverName); Connection con = DriverManager.getConnection(url, userName, password); boolean retry = true; boolean read_rows = true; int retries = 0; int row_num = 0; con.setAutoCommit(false); // create a statement with a scrollable result set that can not be updated // since we are just fetching rows Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); try { rs1 = stmt.executeQuery(query); // fetch a row at a time for proper granularity for a retry effort rs1.setFetchSize(1); while (read_rows) { try { // read the next row if (rs1.next()) { System.out.println("Customer Name: " + rs1.getString(1)); row_num++; } else read_rows = false; // no more rows } catch (SQLException e) { int error_code = e.getErrorCode(); // Throw the execption higher if a row or table lock error is not // detected. Otherwise, continue to try and read a row until // the "timeout" value is reached if ((error_code != OE_ERR_RECORD_LOCKED) && (error_code != OE_ERR_TABLE_LOCKED)) { throw e; } retries++; } if (retries == MAX_FETCH_RETRIES) read_rows = false; } if (retries == MAX_FETCH_RETRIES) System.out.println("ABORT - Unable to read record"); } catch (SQLException e) { System.out.println("Statement failed"); showException(e); } System.out.println("TOTAL ROWS = " + row_num); rs1.close(); // close the cursor for the stmt stmt.close(); // drop the statement con.close(); } catch (SQLException e) { showException(e); } catch (Exception e) { String theReason = "OE_Test: exception thrown"; SQLException error = new SQLException(theReason); showException(error); } finally { System.out.println("\nretryRead exits"); } } private void showException(SQLException theError) { System.err.println("\n"); System.err.println("SQLClient Error: "); System.err.println("SQL State: " + theError.getSQLState()); System.err.println("Vender Code: " + theError.getErrorCode()); System.err.println("Reason: " + theError.getMessage()); } public static void main(String[] args) { new retryRead(args); } } import java.sql.*; public class retryRead { public static final int OE_ERR_RECORD_LOCKED = -210015; public static final int OE_ERR_TABLE_LOCKED = -210005; public static final int MAX_FETCH_RETRIES = 5; /** * */ public retryRead(String[] args) { String driverName = "com.ddtek.jdbc.openedge.OpenEdgeDriver"; String url = "jdbc:datadirect:openedge://" + args[0] + ":" + args[1] + ";databaseName=" + args[2]; String userName = args[3]; String password = args[4]; ResultSet rs1 = null; try { String query = "SELECT name FROM pub.customer"; System.out.println("retryRead entered"); Class.forName(driverName); Connection con = DriverManager.getConnection(url, userName, password); boolean retry = true; boolean read_rows = true; int retries = 0; int row_num = 0; con.setAutoCommit(false); // create a statement with a scrollable result set that can not be updated // since we are just fetching rows Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); try { rs1 = stmt.executeQuery(query); // fetch a row at a time for proper granularity for a retry effort rs1.setFetchSize(1); while (read_rows) { try { // read the next row if (rs1.next()) { System.out.println("Customer Name: " + rs1.getString(1)); row_num++; } else read_rows = false; // no more rows } catch (SQLException e) { int error_code = e.getErrorCode(); // Throw the execption higher if a row or table lock error is not // detected. Otherwise, continue to try and read a row until // the "timeout" value is reached if ((error_code != OE_ERR_RECORD_LOCKED) && (error_code != OE_ERR_TABLE_LOCKED)) { throw e; } retries++; } if (retries == MAX_FETCH_RETRIES) read_rows = false; } if (retries == MAX_FETCH_RETRIES) System.out.println("ABORT - Unable to read record"); } catch (SQLException e) { System.out.println("Statement failed"); showException(e); } System.out.println("TOTAL ROWS = " + row_num); rs1.close(); // close the cursor for the stmt stmt.close(); // drop the statement con.close(); } catch (SQLException e) { showException(e); } catch (Exception e) { String theReason = "OE_Test: exception thrown"; SQLException error = new SQLException(theReason); showException(error); } finally { System.out.println("\nretryRead exits"); } } private void showException(SQLException theError) { System.err.println("\n"); System.err.println("SQLClient Error: "); System.err.println("SQL State: " + theError.getSQLState()); System.err.println("Vender Code: " + theError.getErrorCode()); System.err.println("Reason: " + theError.getMessage()); } public static void main(String[] args) { new retryRead(args); } }

    29. OPS-27: Record and Table Locking In OpenEdge SQL Apps Locking Contention - Readpast Overrriding the lock wait in the storage engine for this query Note, can only be used in READ COMMITTED isolation levelOverrriding the lock wait in the storage engine for this query Note, can only be used in READ COMMITTED isolation level

    30. OPS-27: Record and Table Locking In OpenEdge SQL Apps Locking Contention - Readpast

    31. OPS-27: Record and Table Locking In OpenEdge SQL Apps 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

    32. OPS-27: Record and Table Locking In OpenEdge SQL Apps Locking Contention – NoLock and Rowid import java.sql.*; public class retryRead { public static final int OE_ERR_RECORD_LOCKED = -210015; public static final int OE_ERR_TABLE_LOCKED = -210005; public static final int MAX_FETCH_RETRIES = 5; /** * */ public retryRead(String[] args) { String driverName = "com.ddtek.jdbc.openedge.OpenEdgeDriver"; String url = "jdbc:datadirect:openedge://" + args[0] + ":" + args[1] + ";databaseName=" + args[2]; String userName = args[3]; String password = args[4]; ResultSet rs1 = null; try { String query = "SELECT name FROM pub.customer"; System.out.println("retryRead entered"); Class.forName(driverName); Connection con = DriverManager.getConnection(url, userName, password); boolean retry = true; boolean read_rows = true; int retries = 0; int row_num = 0; con.setAutoCommit(false); // create a statement with a scrollable result set that can not be updated // since we are just fetching rows Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); try { rs1 = stmt.executeQuery(query); // fetch a row at a time for proper granularity for a retry effort rs1.setFetchSize(1); while (read_rows) { try { // read the next row if (rs1.next()) { System.out.println("Customer Name: " + rs1.getString(1)); row_num++; } else read_rows = false; // no more rows } catch (SQLException e) { int error_code = e.getErrorCode(); // Throw the execption higher if a row or table lock error is not // detected. Otherwise, continue to try and read a row until // the "timeout" value is reached if ((error_code != OE_ERR_RECORD_LOCKED) && (error_code != OE_ERR_TABLE_LOCKED)) { throw e; } retries++; } if (retries == MAX_FETCH_RETRIES) read_rows = false; } if (retries == MAX_FETCH_RETRIES) System.out.println("ABORT - Unable to read record"); } catch (SQLException e) { System.out.println("Statement failed"); showException(e); } System.out.println("TOTAL ROWS = " + row_num); rs1.close(); // close the cursor for the stmt stmt.close(); // drop the statement con.close(); } catch (SQLException e) { showException(e); } catch (Exception e) { String theReason = "OE_Test: exception thrown"; SQLException error = new SQLException(theReason); showException(error); } finally { System.out.println("\nretryRead exits"); } } private void showException(SQLException theError) { System.err.println("\n"); System.err.println("SQLClient Error: "); System.err.println("SQL State: " + theError.getSQLState()); System.err.println("Vender Code: " + theError.getErrorCode()); System.err.println("Reason: " + theError.getMessage()); } public static void main(String[] args) { new retryRead(args); } } import java.sql.*; public class retryRead { public static final int OE_ERR_RECORD_LOCKED = -210015; public static final int OE_ERR_TABLE_LOCKED = -210005; public static final int MAX_FETCH_RETRIES = 5; /** * */ public retryRead(String[] args) { String driverName = "com.ddtek.jdbc.openedge.OpenEdgeDriver"; String url = "jdbc:datadirect:openedge://" + args[0] + ":" + args[1] + ";databaseName=" + args[2]; String userName = args[3]; String password = args[4]; ResultSet rs1 = null; try { String query = "SELECT name FROM pub.customer"; System.out.println("retryRead entered"); Class.forName(driverName); Connection con = DriverManager.getConnection(url, userName, password); boolean retry = true; boolean read_rows = true; int retries = 0; int row_num = 0; con.setAutoCommit(false); // create a statement with a scrollable result set that can not be updated // since we are just fetching rows Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); try { rs1 = stmt.executeQuery(query); // fetch a row at a time for proper granularity for a retry effort rs1.setFetchSize(1); while (read_rows) { try { // read the next row if (rs1.next()) { System.out.println("Customer Name: " + rs1.getString(1)); row_num++; } else read_rows = false; // no more rows } catch (SQLException e) { int error_code = e.getErrorCode(); // Throw the execption higher if a row or table lock error is not // detected. Otherwise, continue to try and read a row until // the "timeout" value is reached if ((error_code != OE_ERR_RECORD_LOCKED) && (error_code != OE_ERR_TABLE_LOCKED)) { throw e; } retries++; } if (retries == MAX_FETCH_RETRIES) read_rows = false; } if (retries == MAX_FETCH_RETRIES) System.out.println("ABORT - Unable to read record"); } catch (SQLException e) { System.out.println("Statement failed"); showException(e); } System.out.println("TOTAL ROWS = " + row_num); rs1.close(); // close the cursor for the stmt stmt.close(); // drop the statement con.close(); } catch (SQLException e) { showException(e); } catch (Exception e) { String theReason = "OE_Test: exception thrown"; SQLException error = new SQLException(theReason); showException(error); } finally { System.out.println("\nretryRead exits"); } } private void showException(SQLException theError) { System.err.println("\n"); System.err.println("SQLClient Error: "); System.err.println("SQL State: " + theError.getSQLState()); System.err.println("Vender Code: " + theError.getErrorCode()); System.err.println("Reason: " + theError.getMessage()); } public static void main(String[] args) { new retryRead(args); } }

    33. OPS-27: Record and Table Locking In OpenEdge SQL Apps Locking Contention – NoLock and Rowid

    34. OPS-27: Record and Table Locking In OpenEdge SQL Apps In Summary : Combination ? - ? - ?

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

    36. OPS-27: Record and Table Locking In OpenEdge SQL Apps 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

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

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

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

    40. OPS-27: Record and Table Locking In OpenEdge SQL Apps Row Locks

    41. OPS-27: Record and Table Locking In OpenEdge SQL Apps Row Lock Compatibility

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

    43. OPS-27: Record and Table Locking In OpenEdge SQL Apps Table Lock Compatibility

    44. OPS-27: Record and Table Locking In OpenEdge SQL Apps Basics of transactions - ACID Atomicity Consistency Isolation Durability Atomicity Atomicity refers to the ability of the DBMS to guarantee that either all of the tasks of a transaction are performed or none of them are. For example, the transfer of funds can be completed or it can fail for a multitude of reasons, but atomicity guarantees that one account won't be debited if the other is not credited. Consistency Consistency property ensures that the database remains in a consistent state before the start of the transaction and after the transaction is over (whether successful or not). Isolation Isolation refers to the ability of the application to make operations in a transaction appear isolated from all other operations. This means that no operation outside the transaction can ever see the data in an intermediate state; for example, a bank manager can see the transferred funds on one account or the other, but never on both — even if he ran his query while the transfer was still being processed. More formally, isolation means the transaction history (or schedule) is serializable. This ability is the constraint which is most frequently relaxed for performance reasons. Durability Durability refers to the guarantee that once the user has been notified of success, the transaction will persist, and not be undone. This means it will survive system failure, and that the database system has checked the integrity constraints and won't need to abort the transaction. Many databases implement durability by writing all transactions into a log that can be played back to recreate the system state right before the failure. A transaction can only be deemed committed after it is safely in the log. Atomicity Atomicity refers to the ability of the DBMS to guarantee that either all of the tasks of a transaction are performed or none of them are. For example, the transfer of funds can be completed or it can fail for a multitude of reasons, but atomicity guarantees that one account won't be debited if the other is not credited. Consistency Consistency property ensures that the database remains in a consistent state before the start of the transaction and after the transaction is over (whether successful or not). Isolation Isolation refers to the ability of the application to make operations in a transaction appear isolated from all other operations. This means that no operation outside the transaction can ever see the data in an intermediate state; for example, a bank manager can see the transferred funds on one account or the other, but never on both — even if he ran his query while the transfer was still being processed. More formally, isolation means the transaction history (or schedule) is serializable. This ability is the constraint which is most frequently relaxed for performance reasons. Durability Durability refers to the guarantee that once the user has been notified of success, the transaction will persist, and not be undone. This means it will survive system failure, and that the database system has checked the integrity constraints and won't need to abort the transaction. Many databases implement durability by writing all transactions into a log that can be played back to recreate the system state right before the failure. A transaction can only be deemed committed after it is safely in the log.

More Related