Download
1 / 43
430 likes | 1.33k Vues
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 ? - ? - ?.
E N D
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.
