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

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

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

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

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 :

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

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:

And DBMS_Lock is different from DMBS_lock_sleep:

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

Hi Stefan,

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

Best Regards,

Deepak