Kbase P137553: SQL: How to change the TRANSACTION ISOLATION LEVEL at will using the DB Navigator Perspective of th
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
25/05/2010 |
|
Status: Verified
GOAL:
SQL: How to change the TRANSACTION ISOLATION LEVEL at will using the DB Navigator Perspective of the OpenEdge Architect?
GOAL:
How to alternate between TRANSACTION ISOLATION LEVEL types using OpenEdge Architect DB Navigator Perspective?
GOAL:
How to change TRANSACTION ISOLATION LEVEL in the DB Navigator Perspective when the AutoCommit property of the connection is OFF?
GOAL:
How to change TRANSACTION ISOLATION LEVEL in the DB Navigator Perspective when the AutoCommit property of the connection is ON?
GOAL:
How to COMMIT a transaction in the DB Navigator Perspective when the AutoCommit property of the connection is OFF?
GOAL:
How to establish a read only JDBC connection in the DB Navigator Perspective?
FACT(s) (Environment):
Windows
OpenEdge 10.1x
FIX:
The following steps detail how to change the TRANSACTION ISOLATION LEVEL at will when using the DB Navigator Perspective of the OpenEdge Architect. The steps explain how to do this when the AutoCommit property of the connection is ON and when it is OFF and demonstrate how to manually COMMIT a transaction when the AutoCommit property of the connection is OFF using the Commit Button in the Connections View of the DB Navigator Perspective.
NOTE: Setting the TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED is equivalent to having a read-only connection.
A. When the AutoCommit ON:
1. Execute the following statements to change the TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED and display Customer 1 and Customer 11 records:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM PUB.Customer Where CustNum = 1 OR CustNum = 11;
2. Close the SQL Results View generated by the above SELECT statement and execute the following statements to change the TRANSACTION ISOLATION LEVEL to READ COMMITTED and modify Customer 1 and Customer 11 records:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE PUB.Customer SET Name = 'This is the new customer One name' WHERE Custnum = 1;
UPDATE PUB.Customer SET Name = 'This is the new customer Eleven name' WHERE Custnum = 11;
3. Execute the following statements to change the TRANSACTION ISOLATION LEVEL back to READ UNCOMMITTED and display the modified Customer 1 and Customer 11 records:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM PUB.Customer Where CustNum = 1 OR CustNum = 11;
B. When the AutoCommit OFF:
1. Click the Commit Button in the Connections View and execute the following statements in the SQL Editor to change the TRANSACTION ISOLATION LEVEL to READ COMMITTED and modify Customer 2 and Customer 22 records:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE PUB.Customer SET Name = 'This is the new Second customer name' WHERE Custnum = 2;
UPDATE PUB.Customer SET Name = 'This is the new Twenty Second customer name' WHERE Custnum = 22;
2. Click the Commit Button in the Connections View and execute the following statements in the SQL Editor to change the TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED and display the modified Customer 2 and Customer 22 records:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM PUB.Customer Where CustNum = 2 OR CustNum = 22;
3. Close the SQL Results View generated by the above SELECT statement and click the Commit Button in the Connections View and execute the following statements in the SQL Editor to change the TRANSACTION ISOLATION LEVEL back to READ COMMITTED and modify Customer 3 and Customer 33 records:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE PUB.Customer SET Name = 'This is the new third customer name' WHERE Custnum = 3;
UPDATE PUB.Customer SET Name = 'This is the new Thirty Third Second customer name' WHERE Custnum = 33;