OPS-27: Understanding Record and Table Locking In OpenEdge

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?

3
Unlock the facts Combination ? - ? - ?
4
Basics of Locking
Grades of Locks

• Schema lock
• Table lock
• Record lock

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
6
How locking conflicts can occur
The Lock Table (simplified)
Active Queued (Waiting) Lock Entries
Hash Table
7
Where to monitor locks?
PROMON Option 4 Record Locking Table
8
Where to monitor locks?
OpenEdge Management Locks and Latches page
or VST _LockReq
9
Unlock the facts Combination ? - ? - ?

• Review of locking model and definitions

• Available SQL features to influence locking

• Examples on lock contention strategies

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

11
Basics of Transactions
Transaction scope

• Auto-commit - true

con.setAutoCommit(true)

• Explicit commit/rollback

con.commit() OR con.rollback()
12
Isolation Level Affect on Lock Type
Isolation Level Update Update Fetch Fetch
Isolation Level Table Lock Record Lock Table Lock Record Lock
Read Uncommitted --- --- NoLock NoLock
Read Committed IX Exclusive IS Share
Repeatable Read IX Exclusive IS Share
Serializable SIX Exclusive Share None
13
Basics of Transactions
The Basics

• Setting Isolation level to affect
• transaction strength

con.setTransactionIsolation(
Connection.TRANSACTION_READ_COMMITTED)
14
ODBC Setting Isolation Level
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
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
lt 100 FOR UPDATE
17
Locking Contention or Overflow

• Avoiding lock table overflow error?

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

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
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
21
Unlock the facts Combination ? - ? - ?

• Review of locking model and definitions

• Available SQL features to influence locking

22
Locking Contention

• Still getting lock contention errors?

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

24
Locking Contention Read Retry

• Isolation level Read Committed
• Encounter lock error -210015
• Try-catch block to capture lock error
• Retry fetching the record

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)
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
27
Locking Contention

• Make use of locking hints

SELECT from PUB.Status WITH ()
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

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))

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("Cust
omer Name " rs2.getString(1))

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

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 ?"
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()
34
In Summary Combination ? - ? - ?
35
In Summary Unlocked
-
6
4
-
5
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

37
?
Questions
38
Thank You
39
(No Transcript)
40
Row Locks
Lock Type Allows Blocks
No-lock read nothing
Share (S) read all updates
Exclusive (X) read, update all access by others
NO-LOCK means access without lock
41
Row Lock Compatibility
Lock Request Type
42
Table Lock Types
Lock Type Allow Block
Intent Share (IS) Row S Table X
Intent Exclusive (IX) Row X Table X
Shared (S) Row S Row X
Shared with Intent Exclusive (SIX) Row S, X other Row X
Exclusive (X) Row S, X any other access
43
Table Lock Compatibility
Lock Request Type
IS IX S SIX X
None OK OK OK OK OK
IS OK OK OK - Q - - Q -
IX OK OK - Q - - Q - - Q -
S OK - Q - OK - Q - - Q -
SIX OK - Q - - Q - - Q - - Q -
X - Q - - Q - - Q - - Q - - Q -
Other Active Locks
44
Basics of transactions - ACID

• Atomicity
• Consistency
• Isolation
• Durability