Session management allows you to view tenant sessions, perform session statistics, and detect deadlocks.
Background information
The connection between an application and a database is called a session. On the Sessions tab, you can view Total Sessions, Active Sessions, and Idle Sessions.
Tenant sessions
Log in to the OceanBase Cloud console.
In the left-side navigation pane, click Instances.
In the instance list, find the target instance and click the instance name to go to the Overview page.
In the left-side navigation pane, click Tenants.
In the tenant management list, click the name of the target tenant to go to the tenant overview page.
In the left-side navigation pane, click Diagnostics.
At the bottom of the Diagnostics page, select the Sessions tab.
You can enter SQL in the search box to query sessions.
Select View Only Active Sessions to view active session information in the session list.
Select Aggregate Sessions by SQL ID to summarize sessions with the same SQL ID.
Note
Aggregating sessions by SQL ID helps you analyze the traffic and consumption of different SQL IDs to identify resource consumers.
- Tenant sessions include the following information:
Session information |
Description |
|---|---|
| Session ID | The ID of the current session, which uniquely identifies the session. |
| SQL | The content of the SQL statement currently being executed in the session. |
| User | The database username used to log in to the session. |
| Source | The address from which the session is initiated. |
| Destination | The address to which the session is sent. |
| Database name | The name of the database that the session accesses. |
| Command | The type of command currently being executed in the session. |
| Execution time (s) | The time consumed by the SQL statement currently being executed in the session. You can sort sessions by Execution Time. |
| Status | The status of the current session. Valid values: CLOSED (closed) and ACTIVE (active). |
- Click Close Session in the Actions column of a session to delete the session. You can also select multiple sessions and click Close sessions in batches.
Session statistics
On the Session Statistics tab, you can view or export the number of active sessions and the total number of sessions of the current tenant from the user, access source, and database dimensions.
Click 10s SQL Analysis in the upper-right corner to view SQL execution in the tenant within 10 seconds. You can view statistics by SQL type, User, Source, and Database.
Deadlock detection
OceanBase Database uses multi-version two-phase locking to maintain the correctness of its concurrency control model. The locking mechanism is crucial for ensuring data concurrency and consistency.
The locking mechanism can lead to deadlocks, which occur when there is a cyclic dependency on resources. For example, if transaction A and transaction B both attempt to acquire resources 1 and 2, transaction A might first acquire resource 1 and then try to acquire resource 2, while transaction B might first acquire resource 2 and then try to acquire resource 1. If no transaction is willing to release the resources it has acquired, no transaction can proceed and end normally.
Note
Deadlock detection is supported in OceanBase Database V3.2 and later.
Procedure
The first time you use deadlock detection, click Enable Deadlock Detection. After deadlock detection is enabled, OceanBase Database real-time detects and handles deadlock events, which consumes about 2% of performance. Enable this feature based on your tenant requirements.
After deadlock detection is enabled, the system diagnoses whether deadlocks exist in the tenant and displays the diagnosis results on the Deadlock Details page. The results are retained for 7 days.
You can click Disable Deadlock Detection or Refresh in the upper-right corner to disable or refresh deadlock detection details.