Failure to acquire exclusive schema lock for DDL.

[lochdsmile]

Hi All,

I am new to Progress SQL9.2. I am doing my project that using OpenEdge as a database. My application using JDBC to connect to database and excuting sql statement such as SELECT, DELETE, INSERT, CREATE TRIGGER, DROP TRIGER, CREATE TABLE, DROP TABLE statement (both of DDL and DML).

It is OK when I create table, trigger via JDBC but i get a errors when I DROP TABLE or TRIGER (using JDBC).

Here is the error:

[DataDirect][OpenEdge JDBC Driver][OpenEdge] Failure to
acquire exclusive schema lock for DDL.

Anyone, Please help me to fix it.

Many thanks,

Rokku,

 

[Casper]

Hi,

Maybe a stupid question, but are you absolutely sure no other users are in the database at the moment you try to drop table. I can imagine that a drop table can't be done bacuse someone has on ongoing transaction.

Regards,

Casper.

 

[lochdsmile]

:blush:

1. The first, I am sure that there are no users is accessing the table that I want to drop when dropping because I create this table for private purpose and drop it when I don't need it anymore. I only say that before removing this table, maybe there are some selection on the table but at the time that I drop it, I am sure there are no accessing on it.

2. The second, I also notice you that my application using 3 kind of databases: Oracle 10g, SQL Server 2000 and Progress. But I run well on Oracle and SQL Server. I mean that I didn't get the error when I drop the table on Oracle and SQL Server (also using JDBC). It is OK with Oracle & SQL Server.

3. Therefore I guest that maybe Progress DBA need to setup some parameters for the database.

[SIZE=-1]Does anyone have any idea for it?

Regards,

Rokku.
[/SIZE]

 

[Casper]

Hi there,

The error message you get suggests there is some lock on the schema which prevents you from deleting it. Maybe to delete the table you need a lock on the entire scheme? If so then deleting the table is only possible when no users are connected/accesing the database.

But ok , I must admit I'm not an SQL expert, so I'm guessing here.

Oracle10g and SQLserver have different mechanism (for instance no 4GL users... so locking there is a bit different then locking on a Progress database.

KB P24790: http://tinyurl.com/oc7bf

Regards,

Casper.

 

[lochdsmile]

Thank you Casper for your support,

1. I found on Internet the answer

Error: 'Failed to acquire an exclusive lock for DDL operation'
RN#: 101A-00252
===============
When maintaining user security or altering various database objects
with the schema wizard, or even creating a table with a defined index,
this error may occur if there is more than one user connected to the
database. You must ensure that you are the only connected user with
only one connection before performing any schema change.

2. I tried to test with only user connect to progress database.

- My Application runs well. I didn;t get the error.

3. I tried to use a JDBC tool such as DBVisualizer to open the Progress Database to make sure that there are more than one user connected to the database. Then I tried my function to drop table. I got the error.

4. Therefore I think It is a problem of locking mechanism for SQL (Maybe it runs well for 4GL).

5. Address of document: http://www.psdn.com/library/servlet/KbServlet/download/1248-102-1521/oe101a_readme.pdf

Regards,

Rokku.