You can translate the document:

Goal

Under some circumstances, the Virtual DataPort Server is unable to enter Single User Mode when requested from a connection. This document describes the steps to follow in such a case to find the cause of this issue.

Scope

Note: This article is valid for older versions of the Denodo Platform. For newer versions, in order to avoid any deadlocks there are two configuration settings by default (value in seconds):

com.denodo.vdb.engine.session.transactionTimeout=1800

com.denodo.vdb.engine.session.inactiveTransactionTimeout=30

The first setting determines how long a transaction can last at maximum before reaching a timeout. The default value is 1800 seconds (30 minutes). The second setting describes how long a transaction can remain inactive, i.e. without executing a command, before it will reach a timeout and will be rollbacked. The default timeout for that one is 30 seconds.

Both of these options help avoiding deadlocks in the Virtual DataPort Server. You can find more information in the section Transactions in Virtual DataPort.

Furthermore, there were some slight changes introduced with Denodo 8.0:

1. The MBeans CatalogManagementInfo, RequestManagementInfo and TransactionsManagementInfo do not exist anymore. Instead, for Denodo 8.0 you would use the VDBServerRequestsManagementInfo node for observing the requests.

2. In Denodo 8.0, you need to use the port 9997 instead of the port 9999 for establishing a JMX connection.

Content

When the Single User Mode is established from a given connection, only the sentences executed from that connection will be immediately attended by the Virtual DataPort Server. The sentences emitted from other active connections will be queued until the server exits the single user mode.

The Single User Mode can be requested in an explicit way from a connection using the “ENTER SINGLE USER MODE” VQL command but it can also be requested automatically when a connection is trying to run a VQL command that alters the metadata of the Virtual DataPort Server, for instance, when creating or modifying a view or data source or when creating new users or databases or assigning privileges to users.

For a connection to enter Single User Mode, no transactions can be opened and no requests can be active at that moment. In that case, the VDP server will wait to enter Single User Mode until the transaction or the request is completed.

Taking this into account, it is possible that a client tool trying to enter Single User Mode will have to wait indefinitely until a long-running query finishes or a transaction is committed looking like the VDP server is not responding. In such a case, it will be useful to identify if there is an active operation blocking the Single User Mode.

The following steps describe how to identify the possible cause of the problem.

If the requests log is enabled, the first step will be to analyze the DENODO_HOME/logs/vdp/vdp-requests.log file to check if there is any statement that could  provoke the lock (inserts on a view or an alter view statement, for instance).

If further information is required to identify the sentence that is causing the lock, it is also possible to analyze the Denodo Monitor query logs looking for a startRequest entry that does not have a matching endRequest line, meaning that the request is still running.

If the requests log is not enabled or the Denodo Monitor is not running, a JMX client like JConsole can be used to monitor current transactions activity in the server. The JMX Mbean needed to get this information is :

com.denodo.vdb.management.mbeans:type=TransactionsManagementInfo,databaseName=<databaseName>

Note that all the databases in the VDP server must be analyzed to find any active transaction.

Here is an example describing how to use JConsole to find out what sentence is causing the lock.

JConsole is a JMX client distributed with any Java JDK installation. The JConsole binary will be included under the bin directory of a JDK installation.

Once the JConsole is started, follow the steps described below to connect to the VDP server.

  1. Select Remote Process.
  2. Fill the proper VDP server connection string, for instance “myhost:9999
  3. Fill the user and password with a VDP user with JMX access.

After connecting, select the MBeans tab.

The information regarding the requests can be found under com.denodo.vdb.management.mbeans > RequestManagementInfo. There is a Mbean available for each database created in the VDP server.

 

Selecting a database, it is possible to look at the available attributes, operations and notifications.

Note that the Mbean:

com.denodo.vdb.management.mbeans:type=VDBServerManagementInfo

contains an attribute named “Active transactions”. This parameter retrieves the total number of active transactions. Clicking on the “Active transactions” attribute, the following screen is shown.

In case of any active transaction in the database, the number of transactions will be greater than zero.

It is also possible to subscribe to the notification buffer to receive information related to the events about transactions.

This information can be useful to get more information about the status of a transaction as well as its origin: database involved, transaction start time, etc.

We will see now by means of an example how to detect what is preventing the VDP server from entering Single User Mode. To reproduce the blocking situation we are going to use the following tools:

  1. A third-party SQL client, in this case DbVisualizer, connected to the Virtual DataPort server running an insert on a base view. Please, note that the connection has to be configured with setAutocommit=false to force the lock. To do that, go to the connection details and uncheck the Auto Commit field.

  1. A VDP Admin Tool running the command “ENTER SINGLE USER MODE”. This command will be blocked by the insert command executed from DbVisualizer first.

The steps to reproduce the lock are the following:

  1. Run an insert on DbVisualizer. For instance:

        

                INSERT INTO  bv_blocktest (c1 ,c2,c3)

VALUES(1,'a','b');

The sentence is an insert to a base view coming from a relational data source imported into a Virtual DataPort database.

Since the setAutocommit property is set to false, DbVisualizer will ask to run a commit or a rollback.

  1. Before clicking on Commit, Rollback or Cancel, Open a VDP Administration Tool and try to execute the command “ENTER SINGLE USER MODE”.

DBVisualizer has an open transaction and, therefore, The VDP Administration Tool will have to wait for the transaction to be completed and it will look like the Virtual DataPort Server is not responding.

waiting_for_single_user_mode_to_finish.png

  1. At this point, it is possible to use JConsole to find out what is blocking the VDP Administration Tool.

After refreshing the ActiveTransactions value JConsole shows the following results.

                

From this information we can identify the transaction that is blocking the Single User Mode.

It is possible to get more information if Jconsole is subscribed to the notifications.

Go to notifications in the selected MBean and check if the last event is a “startTransaction” event.  In such a case, double clicking on the User Data column will show the status of the transaction.

                

active_transaction.png

Taking a look at the active transaction, it is possible to see the target database name and the session id, which will be important to find out the client that is executing the query involved in the transaction.

Once we have the database name, it is possible to get the open sessions in the Mbean VDBServerManagementInfo, using the operation getSessions.

The getSessions operation receives the database name as input parameter and retrieves the active sessions for the database. The following information will be available for each session.

  • Database name
  • Connection identifier
  • Connection start time
  • Client ip
  • User-agent: for instance, “Denodo-VDP-AdminTool”
  • Access interface: e.g “VDP-AdminTool”
  • Session id
  • Session status
  • Query running
  • Query running identifier
  • Query running queued

Invoking the getSessions operation with the database name and then using the session id we can obtain the connection id for the connection that is locking the server.



connection_id.png

Once the blocking connection has been identified, it is possible to select its identifier and close the connection from JConsole.

To do that, go to the MBean VDBServerManagementInfo > Operations and select the  “closeConnectionById” operation.

In order to invoke the operation, fill the id parameter and click on the “closeConnectionById” button.

close_connection.png

Summarizing, in this example, we have found that a transaction opened from DBVisualizer was blocking the VDP server and the VDP Admin Tool connection could not enter SINGLE USER MODE. Once the transaction is identified we have used a JMX operation to close the blocking connection.

Disclaimer
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.

Questions

Ask a question

You must sign in to ask a question. If you do not have an account, you can register here