Kbase P79681: Is it normal behavior to have share locks on Progress system tables when opening a Microsoft Access
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
4/24/2009 |
|
Status: Verified
SYMPTOM(s):
Opening a Microsoft Access linked table places share locks on a number of Progress system tables.
Promon shows share locks for system table number -2 (_field).
Some linked tables also show share locks for table numbers -80 (_Sysviews) and -84 (_Syssynonyms).
Isolation level within the DSN is set to Read Uncommitted.
FACT(s) (Environment):
All Supported Operating Systems
Progress/OpenEdge Versions
OpenEdge SQL Category: SQL-92 Clients
CAUSE:
This is expected behavior.
A SQL-92 query, in order to properly execute, needs to read the schema for:
1. table definitions
2. view definitions
3. synonym definitions
4. authorization
5. other information, depending on the SQL statement and the schema content.
Some of the reads are done without locking and some are done with normal locking.
FIX:
This is normal behavior when Microsoft Access, using ODBC, opens a linked table to a Progress database.
Run the SQL tracing feature of the Microsoft ODBC Data Source Administrator and promon to determine which tables are accessed upon the opening of the linked table.
1. Open the ODBC Manager tool and select the tracing tab.
2. Archive the current sql.log file if present.
3. Select the Start Tracing Now button.
4. Open a newly linked table in Access.
5. View the locks in Promon under #4, then #1.
Example showing Lock on _field table:
Record Locking Table:
Usr Name Chain # Rec-id Table Lock Flags
38 admin REC 141 6328 -2 SHR
6. Or view the locks via a query of the _lock table.
7. Shut down the opened link.
8. Turn off SQL tracing.
9. View the sql.log file for a list of all the tables accessed when opening the linked table for the first time.
10. Open the linked table a second time to view locks and verify no share locks occur on the system tables because the query uses cached information.