Warnings in the log related to DBMS_LOCK (Oracle), but DBMS is deprecated

Stefan Hall shared this problem 26 days ago
Resolved

We are currently using version 9.8.0.1 and see some warnings in catalina.yyyy-mm-dd.log regarding DBMS_LOCK.

WARN [main] liquibase.lockservice.null Error during capability test for oracle session locking: java.sql.SQLSyntaxErrorException: ORA-00942: Tabelle oder View nicht vorhanden

WARN [main] liquibase.lockservice.null Oracle user needs execute access on DBMS_LOCK, and select access on SYS.V_$LOCK and SYS.V_$SESSION to use session locking
So it seems something is not working as it should. What is it exactly?


Our DBA do not want to assign this permission anymore, because DBMS_LOCK-SLEEP is deprecated. Oracle has replaced the function with DBMS_SESSION.SLEEP, which does not require separate permissions.


If "SLEEP" is the reason, please adjust your source code at least for Oracle DB version 18+.

;) Stefan

Replies (6)

photo
1

Hi Stefan,

Thanks for reaching out.

A lock is placed ( on table or row level - depending on the db) when some query needs to be executed against that table - this makes sure that no other connection can alter/modify this table while its been used by a different connection.

And warnings in YF may not necessarily mean that something stopped working. It may be that a check is made and YF thinks that it may not function because of the access.

Could you please let me know if Oracle is being used as data source? or as a config db?

And also can you please confirm if the warning message goes away if we give the access to Oracle user temporarily (to test)?

Warm Regards,

Deepak

photo
1

Hi Deepak,
we use Oracle for the YF repository DB and also for our data source. Unfortunately I can't test this, all our systems are hardened.

The Oracle function you are using is deprecated.

;) Stefan

photo
1

Hi Stefan,

I have reached out to wider team for inputs regarding the DBMS lock. Here are the comments:

"This is a function of the Liquibase session locking library that was added recently to Yellowfin (which works for MySQL, Postgres and Oracle). Prior to Session Locking being added, liquibase is locked by writing a row to the DATABASECHANGELOCK table. However this cause problems if the process crashes and leaves the record there.

The Session Lock uses a database lock to lock, and block other processes from processing data at the same time.

The Oracle lock does require permissions.. However Yellowfin will test for permissions, and if it doesn't have access it will revert to the table row locking mechanism."

The warning messages that we are seeing are the result of the Oracle test failing. However, these can be ignored if we are happy with the table lock mechanism.

Please let me know if there is anything that I can assist with.

Warm Regards,

Deepak

photo
1

Hi Deepak,
good to know that it somehow works anyway.

But I'm sure you have contact with liquibase or can use your influence. The Oracle function used is deprecated and there is a better new way for Oracle since many versions. An adaptation would be desirable.

;) Stefan

photo
photo
1

Hi Stefan,

The actual method that YF always been using was to lock by adding a row to DATABASECHANGELOCK tab;e. But since we have an additional option (Session lock), YF would first check for the same and if it fails to get permissions, then it would fall back to the original method of inserting a row.

I don't think this process can be altered since its not just Oracle db, but its the same for Postgres, MySQL. (And not really sure if DBMS_LOCK-SLEEP is the actual function that YF is using in oracle db). I believe something like the one outlined is being implemented: https://github.com/blagerweij/liquibase-sessionlock :

bb4c580ba0f9c40bed363fae20de1c62

And it relies on DBMS_LOCK: https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_LOCK.html#GUID-4DECD58D-6D07-4723-B275-40E97902032C (And not the Sleep function)

Please let me know if there is anything that I can assist with.

Thanks,

Deepak

photo
1

Hi Deepak,

I am not a DB expert. If you /YF think that everything is OK and Oracle's advice does not apply to your product, then I have to believe it.

However, the warnings in the log file are annoying, because there is a solution from Oracle, which does not need extended permissions. So YF would have to do something and not me.
Do you see it differently?
;) Stefan

photo
photo
1

Hi Stefan,

As we can see from the warning message that its the DBMS_LOCK (not the DBMS.lock.sleep) : "WARN [main] liquibase.lockservice.null Oracle user needs execute access on DBMS_LOCK, and select access on SYS.V_$LOCK and SYS.V_$SESSION to use session locking"

And DBMS is what liquibase seems to be using:

4769da793a4accf2e1014793b481ad26


And DBMS_Lock is different from DMBS_lock_sleep:

bd0d9fd33fff69f4a353024c58519915


By looking at these, I believe the oracle's doesn't apply to our scenario. Please let me know if there is anything I can assist with.

Regards,

Deepak

photo
1

Hi Deepak,

I have no further questions and will have to live with the warnings. Thank you for your support, you can close the issue.

;) Stefan

photo
photo
1

Hi Stefan,

Sure, I will go ahead and mark this as completed.

Best Regards,

Deepak

Leave a Comment
 
Attach a file