View Incident Report

Serious

Reported By: Uli Becker
Reported On: 8/29/2010
For: Version 2.04 Build 1

# 3302 A View that References Other Databases Can Cause Write Lock Errors on Other Databases with View Open

I have two applications: Datamed and Informa.

Datamed accesses both the datamed- and the informa-database, informa only the informa-database.

When Datamed was running and I started Informa, Informa seemed to hang. The reason for that was the execution of a script at startup that tries to change some procedures in the Informa-database. Since I know that this database can be opened, I put everything in a BEGIN-EXCEPTION-END block

After a lot of experiments I found out that the view "Wartezimmer" in DATAMED is responsible for that.
When this view is opened the script seems to hang. Actually it displays an error message after about 150 seconds. (The bug report is attached).

After that Informa has to be killed, EDBServer cannot remove the session and has to be restarted.

I was able to reduce the problem, so that it can be demonstrated just using EDBManager (2.03 Build21). Do the following steps:

1. Start EDBManager and open just the view "Wartezimmer" in DATAMED.
2. Start a second EDBManager and run the attached script in INFORMA.

If "stop on Exceptions" is checked you get an error message after about 150 seconds (see attached screenshot), if not, the script is executed successfully in about 100 seconds. That's the only difference: the application raises an error, EDBManager completes the script.

CREATE VIEW "Wartezimmer" AS
select status,
FormatPatientSimple( p.name,p.vorname) as Patient,
p.gebdat,
s.Privat,
DatumLast,
DatumWartezimmer,
FormatUhrzeit(DatumWartezimmer) as Uhrzeit,
FormatWartezeit(DatumWartezimmer) as Wartezeit,
Gruppe,
s.PatientenID,
ScheineID,
if (datumlast >= datumwartezimmer then true else false) as LeistungenOK
from scheine s join informa.patienten p
on s.patientenid = p.patientenID
where wartezimmer = true
order by DatumWartezimmer,Patient
DESCRIPTION 'Zeigt alle Patienten im Wartezimmer an'

Comments and Workarounds

The problem is the view referencing another database. ElevateDB wasn't actually designed to work that way, but it is allowing it anyways, and thus causing this issue because it isn't releasing any read locks acquired on the other database catalog properly. However, there isn't any harm in allowing views to reference other queries once this locking issue is fixed, so this feature will stay.

In general, one should be careful about introducing such dependencies on another database into a database.

Resolution

Fixed Problem on 8/29/2010 in version 2.04 build 2

Products Affected

ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap