Detecting and analyzing database lock situations isn't an easy task in general, so the applications generated using the GeneXus Java generator can be monitored using JMX to help with that task.
To detect locks in the database, different tools can be used (depending on the DBMS) to access the database and provide information about the JDBC connections that are locking the resources. After having identified the locks in the database, in general it's necessary to identify the actual user who is executing that lock, among other useful information.
First, remember that web applications use a connection pool to manage the JDBC connections to the database and that this pool is owned by the User configured in the data store properties (UserName Property, User Password Property).
With JMX you can obtain the additional information you need:
- The actual user who is executing the connection, through the PhysicalId attribute of the JDBC connection, and the IP and UserId attribute of the JMX User. This has proven to be very useful in intranet applications.
- In addition, given the JDBC connection that is causing the lock, you can identify the GeneXus object which is executing (LastObject property) and the SQL statement being executed (LastSQLStatement property).
Consider a web application using iSeries DBMS. When a lock is detected, the JOB (QZDASOINIT) number which is locking can be identified using the iSeries console. The QZDASOINIT job corresponds to a JDBC connection.
The physical ID attribute of the JDBC connection in JMX console matches the JOB number of the QZDASOINIT.
So given the JOB number, you can get the corresponding JDBC connection.
Figure #1. wrkactjob command executed on the iSeries console
Go to the pack GeneXusApplicationServer.ApplicationName.DataStore.ConnectionPool.Connection and look at the JDBC connections. Try to find a connection whose PhysicalId matches the QZDASONIT number.
Figure #2. PhysicalId attribute of JDBC connection
It may be necessary to know who the actual user of the JDBC connection is when you need to perform an action over that connection (kill the connection that is locking the resources, for instance).
Given that in web applications there is no relation between the actual user of the application and the user of the JDBC connection, getting that information is not an easy task.
The LastUserAssigned property of the JDBC connection gives the ID of the JMX user.
Figure #3. LastUserAssigned JMX property
Search for the JMX user given its ID, and look at its attributes under the GeneXusApplicationServer.ApplicationName.User pack. The IP attribute of the JMX user is the IP of the PC from where the request was established.
Figure #4. Ip property of a JMX user
In the web application, if the setUserId function is used, the user can be saved in the context and retrieved using the UserId Function. The UserId property of the JMX user returns the same as the UserId function.
The setUserId function needs to be executed each time the connection is given to the user (it can be in the Before Connect or in the After Connect).
Consider the following code in the proc which runs After Connect:
&username = &websession.Get("USERNAME") //The &websession has been saved after the login.
&er = setuserid(&username)
In this example, the UserId value saved using the SetUserId function can be retrieved from the JMX console, at the GeneXusApplicationServer.ApplicationName.User pack:
Figure #5. UserId property of JMX user
Monitoring Java applications