Kbase P131782: Unexpected SQL lock when querying tables from different SQL sessions
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
01/07/2008 |
|
Status: Unverified
SYMPTOM(s):
SQL sessions encountering unexpected record locks when creating records
Tables used in queries hold temporary information for short periods of time
Session #1 running several SQL statements prior to committing:
DELETE FROM table1WHERE...
DELETE FROM table1WHERE...
INSERT INTO table1 SELECT * FROM table2 WHERE...
UPDATE table1 WHERE...
INSERT INTO table2 SELECT * FROM table1 WHERE...
Session #2 attempts to execute statement but receives locking error
INSERT INTO table1 SELECT * FROM table2 WHERE...
[DataDirect][OpenEdge JDBC Driver][OpenEdge] Failure getting record lock on a record from table
Using Read Committed Isolation Level
PROMON Record Locking Table shows Exclusive locks in both table1 and table2
Lock issue disappears temporarily after UPDATE STATISTICS has been run
Lock issue disappears temporarily after the database has been dumped and reloaded
SQL query plan shows a table scan prior to UPDATE STATISTICS or dump and load and problem occurs
SQL query plan shows an index scan after to UPDATE STATISTICS or dump and load and problem does not occur
WHERE clauses of queries do not satisfy all fields of the available indexes
FACT(s) (Environment):
OpenEdge 10.1x
All Supported Operating Systems
CAUSE:
This is expected behaviour. In this scenario:
- The tables used in the queries contained few records.
- The WHERE clause of the queries did not satisfy all fields used in the available index.
- The index contained a VARCHAR field so the row must be read in order to get the value i.e. the value cannot be obtained from the index key alone.
The SQL optimizer can find that a table scan will be cheaper than an index scan. If the record being searched for is located after a locked record then the lock must be resolved before the search is completed.
Even if an index scan is used, it does not prevent the problem 100% of the time as the issue can be encountered in the event of an index lock.
FIX:
To alleviate the issue, one or more of the following suggestions could be implemented:
1) Add a retry mechanism to the code.
2) Add/Modify indexes.
3) Run UPDATE STATISTICS regularly.