Short description of the following content

This document will show you how to solve the server error am.cantLockAsset - Asset is used by someone else. Please retry later. (in service-client logfile: Failed to lock (already locked in db))

Important hints

There are several situations where the error "am.cantLockAsset" could appear which means that the recently edited asset can't be locked.

  • if a censhare-Client crashes

  • if a server command has not finished successfully (yet)

General information

The asset management configuration file is located at ~/css/app/services/asset/config.xml and looks like this:

<?xml version="1.0" encoding="UTF-8" ?>
<assetmanager
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="assetmanager.xsd"
    version="1"
    enabled="true"
    max-invocations="10"
    default-filesystem="assets"
    for-update-clause="WAIT 5"
    max-lock-retries="10"

    asset-cache-size="1000"
    asset-cache-query-junk-size="40"
    asset-cache-joined-queryhelper-timeout="120000"
    asset-cache-expiration-time="300000"
    storage-item-allow-check-doublet="false"
    
    >
    <!-- comment node for admin client -->
    <comment/>

</assetmanager>
CODE

At the default it tries to lock an asset for 5 seconds (for-update-clause="WAIT 5") within the Oracle database and retries that 10 times (max-lock-retries="10").
If it is still not possible to lock the asset the error message will be thrown.

Question/Symptom

Normally these locks are automatically removed after five minutes by the DatabaseServiceWatchdog because it removes the database connection. If this is not happening, the censhare server automatically cleans up hanging database transactions after one hour by the SessionCleaner. If the lock is still there after one hour the database transaction could not be released by the censhare Server. Then you have to do this step manually via SQL within the Oracle database. So if this happens, please try to open the asset again after approx. 10 minutes. If there is still a lock on the asset after waiting 60 minutes then please do the following steps manually.

Answer/Solution

1. Please check the censhare Server logfile if the DatabaseServiceWatchdog is running. If not, a censhare Server restart might be necessary.

corpus@host:~$ grep DatabaseServiceWatchdog ~/work/logs/server-0.0.log
2012.04.19-10:02:24.023 INFO   : DatabaseServiceWatchdog: DatabaseService: DatabaseServiceWatchdog: corpus: closing jdbc connection[2161]
2012.04.19-10:02:24.123 INFO   : DatabaseServiceWatchdog: DatabaseService: DatabaseServiceWatchdog: corpus: opened jdbc connection[2165]
2012.04.19-10:02:24.225 INFO   : DatabaseServiceWatchdog: DatabaseService: DatabaseServiceWatchdog: corpus: opened jdbc connection[2166]
2012.04.19-10:07:24.226 INFO   : DatabaseServiceWatchdog: DatabaseService: DatabaseServiceWatchdog: corpus: checking connections (used: 0, unused: 5, total: 5)
2012.04.19-10:07:24.226 INFO   : DatabaseServiceWatchdog: DatabaseService: DatabaseServiceWatchdog: corpus: connection[2165] is available
2012.04.19-10:07:24.226 INFO   : DatabaseServiceWatchdog: DatabaseService: DatabaseServiceWatchdog: corpus: connection[2166] is available
2012.04.19-10:07:24.226 INFO   : DatabaseServiceWatchdog: DatabaseService: DatabaseServiceWatchdog: corpus: connection[2163] is available
2012.04.19-10:07:24.226 INFO   : DatabaseServiceWatchdog: DatabaseService: DatabaseServiceWatchdog: corpus: connection[2162] expired
2012.04.19-10:07:24.226 INFO   : DatabaseServiceWatchdog: DatabaseService: DatabaseServiceWatchdog: corpus: closing jdbc connection[2162]
2012.04.19-10:07:24.328 INFO   : DatabaseServiceWatchdog: DatabaseService: DatabaseServiceWatchdog: corpus: opened jdbc connection[2167]
CODE

2. Try to lock the asset manually in the Oracle database without censhare by using the following SQL statement - if this results in a resource busy error message, check step 3.

SQL> select name from asset where id=9299820 and currversion = 0 for update wait 5;
CODE

3. Check the Oracle database if there are open sessions older than approx. 30 minutes by using the following SQL statement as CORPUS or SYSDBA - if yes kill these sessions of the CORPUS database user (in some installations the CORPUS database user was adapted and note - if using an Amazon RDS database you need to use the statement from step 4 to terminate the Oracle session otherwise you'll get an "ORA-01031: insufficient privileges") and watch the censhare server logfile for exception stacktraces or closed connection messages. If using PostgreSQL database then you need to use the statement from step 5 to terminate the PostgreSQL sessions.

set lines 200 pages 100
SELECT
q'<ALTER SYSTEM KILL SESSION '>'||s.sid||','||s.serial#||q'<' IMMEDIATE /* >'|| s.username||' '||TO_CHAR(LOGON_TIME, 'YYYY-MM-DD HH24:MI')||' */;'
FROM v$session s
where s.LOGON_TIME < sysdate - 1/12  and s.type != 'BACKGROUND' and username  like 'CORPUS%'
order by LOGON_TIME;
CODE

The asset should now be lockable again (check out, check out abort).

4. After running KILL SESSION  commands on Oracle, please verify afterwards the Oracle Listener is still running and start it manually if necessary.

oracle@oracle-server:~$ lsnrctl status

LSNRCTL for Solaris: Version 11.2.0.4.0 - Production on 06-JUL-2022 12:23:59

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Solaris Error: 146: Connection refused
BASH

In that case, just start it again, DB connection should work after a few seconds afterwards

oracle@oracle-server:~$ lsnrctl start

LSNRCTL for Solaris: Version 11.2.0.4.0 - Production on 06-JUL-2022 12:24:21

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.4/db_1/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 11.2.0.4.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/oracle-server/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle-server)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 11.2.0.4.0 - Production
Start Date                06-JUL-2022 12:24:22
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle-server/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle-server)(PORT=1521)))
The listener supports no services
The command completed successfully
BASH


5. Terminate PostgreSQL Session

Check the PostgreSQL database especially "corpus" database, if there are "idle" sessions older than approx. 30 minutes by using the following SQL statement as CORPUS user - if yes, kill these sessions of the CORPUS database user.

Following command will provide the information of username, client_IP_address, proc_id, exact_ query_information and query_start time.

postgres=# \c corpus corpus
You are now connected to database "corpus" as user "corpus".
corpus=> \pset format wrapped \pset linestyle unicod \pset columns 180;
corpus=> Select 'select pg_terminate_backend('||pid||') from pg_stat_activity;' kill_query,usename,state,client_addr,query,query_start From pg_stat_activity where datname='corpus' AND pid <> pg_backend_pid() AND state = 'idle' AND state_change < current_timestamp - INTERVAL '30' MINUTE;
CODE

After running the above command, it will provide "proc_id's" with other information's and terminate the idle sessions by the following command:

corpus=> select pg_terminate_backend(<proc id>) from pg_stat_activity;
CODE

The asset should now be lockable again (check out, check out abort).