The Mystery of OceanBase Session IDs

1. Why I Wrote This

Three things happened that made me want to properly untangle the knowledge around session IDs.

1. I found that the OceanBase session ID queried within a single transaction can change (actually it doesn’t really change — the reason is explained below).
2. When using the kill command with a queried session ID, it reported that the ID couldn’t be found (not because the ID truly didn’t exist or had changed).
3. Through different query methods, I found many ID values related to session IDs, but I couldn’t organize them into a knowledge network in my head, and I didn’t understand why some of the IDs were designed the way they are.

An Overview of Session IDs

A session ID is used to describe the access link between a client and the database. The OceanBase database architecture uses ODP (generally also called a proxy, and below I’ll use “proxy” to refer to it), so a client’s access link to the database is split into two segments: the first is the connection between the client and ODP (a Client session, which I’ll refer to below as a C connection), and the second is the connection between ODP and OBServer (a Server session, which I’ll mostly refer to below as an S connection).

A single client connection uses one C connection, which then corresponds to multiple S connections; the relationship between a C connection and S connections is 1:N. It’s worth noting that when executing a single SQL statement, at any given moment only one S connection is serving (for now, let’s set aside the secondary routing introduced by remote plans or distributed plans).

So here’s the question: these IDs used to define just 2 connection types — yet you can find 10 different term definitions in the official documentation:

• ID
• session id
• client session id
• server session id
• connection id
• proxy_sessid
• cs_id
• server_sessid
• ss_id
• MASTER_SESSID

Why is that?

2. My Approach to Writing This

When I started researching this topic, I wanted to write a “past and present of the Session ID.” But after digging through the docs, talking to people, and testing things myself, I found there were even more questions than before…

After a few drafts that I rejected myself, it dawned on me: chasing detail and completeness was ultimately what kept me from finishing the article. A mental framework for understanding is what we actually need. Through such a framework you can string together the knowledge and then solve problems when you encounter them — that’s what’s truly valuable.

3. Introducing Session IDs

Let’s start with a figure:

The figure intuitively shows the link relationship between the client and the database (ODP + OBServer).

To make the following description easier, let’s name the 4 connection lines in the figure. The link between the client and ODP is called C-1, and the 3 links by which ODP connects to OBServer are called S-1, S-2, S-3 (below, unless otherwise noted, we don’t consider the direct-connection case).

Now there are some things you need to think through clearly (it may get a bit tangled):

1. C-1, S-1, S-2, and S-3 are all independent connections, each backed by its own ID. Moreover, the components at the two ends of a connection (for example, the two ends of S-1 correspond to ODP and OBServer respectively) may assign different IDs to the same connection S-1. There are many possible reasons to design things so that different components number S-1 with different IDs. How many can you think of?
2. An entire session (including C-1, S-1, S-2, S-3) is exclusively held as long as it isn’t disconnected or ended. No other session can share any of its links.
3. The execution of a single SQL only uses one combination, C-1 + S-X (here S-X means one of the 3 S connections). That is, for one SQL only one S connection participates in serving (we don’t consider secondary routing here).
4. Different SQL statements in the same transaction will use different connection combinations — this is also ODP’s intra-transaction routing feature, which lets more SQL statements use a local execution plan.
5. For views that show sessions, the convention is to display one session per row (because at any given moment, only one SQL can execute in a session). Actually OceanBase has 2 display modes here: show processlist is at the granularity of a session’s C connection, while show full processlist shows the granularity of S connections (so there will be multiple rows). As you can imagine, within the same session at most one is working and the rest are sleeping.
6. When a client connects to the database for the first time and gets a successful connection back, it means the C-1 + S-1 link has been established; at this point S-2 and S-3 don’t yet exist — they aren’t established until ODP needs to connect.

Returning to the many terms above, here’s a preliminary explanation:

• There are only 2 connection types, the C connection and the S connection.
• There are a total of 4 different encodings that describe them (the editor understands “encoding” here to mean a sequence number or ID); all the other terms are aliases for one of these 4.
  • cs_id: this is the encoding generated by the proxy to describe a C connection, and it’s also one of the most common session-id expressions. The ID column in show processlist usually refers to this cs_id (note: usually).
  • proxy_sessid: this is also generated by the proxy to describe a C connection, but it’s somewhat longer. When the proxy establishes a connection with OBServer, the proxy tells OBServer, “the encoding of the C connection of my upstream service is this.” (Note that the proxy doesn’t tell the downstream that the encoding is cs_id, but rather proxy_sessid. A simple way to remember it: for the same C connection, the proxy uses cs_id toward the client and proxy_sessid toward the OBServer side. Hang in there, don’t get dizzy.)
  • server_sessid: generated by OBServer to describe an S connection. It’s unique across the entire OceanBase cluster and is heavily used in OBServer — for example, it’s the one used in the OB_LOCKS view.
  • ss_id: generated by the proxy to describe an S connection. Very few places use this encoding, so just be aware of it.

Summary: the proxy generates 3 encodings, including cs_id, proxy_sessid, and ss_id. Of these, cs_id and ss_id lean toward internal use within the proxy, while proxy_sessid is passed to OBServer, where you may need to query it in OBServer’s logs. OBServer generates one encoding, server_sessid, used to define the session id of an S connection. In the direct-connection case, there is only server_sessid, because the other 3 are all used within the proxy system.

If you can untangle that, then move on to the next level. First, get acquainted with these session-related terms:

• ID: under different query scenarios, this column sometimes shows the cs_id value and sometimes the server_sessid value.
• session id: a generic reference to a session’s encoding, appearing throughout the OceanBase official docs; query tables generally don’t have this field.
• client session id: a new-version cs_id; later we’ll explain why cs_id needed to be redesigned. Sometimes it also refers to a C connection.
• server session id: this is server_sessid; sometimes it also refers to an S connection.
• connection id: obtained by a MySQL tenant using the connection_id() function — it’s server_sessid. Note that when using obclient to connect to the proxy, the login information outputs “Your Mysql connection id is xxx,” and the id shown there is cs_id.

• MASTER_SESSID: this one is special; it refers to the primary server_sessid in a distributed query and does not belong to an S connection (not discussed in this article).

4. Examples of Querying Session IDs

Below is a simulated example, through which you can better understand the relevant concepts.

In the architecture, one client connects to one proxy, and the proxy then connects to 2 OBServers behind it.

That is, C-1 corresponds to S-1 and S-2 behind it.

After making sure C-1, S-1, and S-2 have all been activated, run the following commands:

show proxysession;

show proxysession attribute;

show processlist;

show full processlist;

select connection_id();

select * from gv$ob_session;

select * from gv$ob_processlist;

These respectively yield the following results:

• show proxysession;
  • The id here is cs_id.
  • You can’t run this command on a direct connection (you need a proxy).

• show proxysession attribute; this shows the details of this cs_id. This cs_id corresponds to 2 server_sessids, 3221550874 and 3221876366; their difference is in the info column:
  • “last used ss” means this is the S connection I used when running this query SQL.
  • “ss pool” means this S connection has already been established and was used before.

Likewise, this command can’t be run on a direct connection, because only the proxy can handle it.

• show processlist;
  • When using a proxy, this ID is cs_id. Note that show processlist in the proxy context queries the sessions on this proxy from the perspective of C connections (i.e., one row per client connection).
  • When using a direct connection, this ID is server_sessid.

• show full processlist;
  • This views all S connections; the ID here is server_sessid.
  • The parts highlighted in blue in the figure all belong to the same C connection — you can cross-reference the content of show proxysession attribute.

• select connection_id();
  • What’s queried is the S connection executing this SQL — it’s server_sessid.

• select * from gv$ob_session;
  • The parts highlighted in blue here all belong to the same C connection; they share the same proxy_sessid. By rights there should be only 2 S connections, so why are there 4 rows corresponding to 4 IDs?
  • You’ll find that of the 4 IDs, 2 are server_sessids that appeared in the earlier examples, and the remaining 2 are new server_sessids.
  • Note the info: PX DFO EXECUTING means distributed execution. Because the gv$ob_session query needs to look at relevant information on all OBServers, the OBServer that actually executes this proxy_sessid’s SQL establishes new connections (the new distributed-execution connections backing the S connections) and queries the internal information of the OBServers respectively.
  • We can understand it via other columns (such as TRACE_ID, HOST, etc.).

This also shows that the access connections of an OceanBase database are fairly complex. In this case the connection is split into 3 segments: the first is client to proxy, the second is proxy to OBServer, and the third is OBServer to OBServer (it also needs to re-access itself).

• select * from gv$ob_processlist;
  • This gives the same result as show full processlist.
  • The ID represents server_sessid, and it won’t show the follow-on connections of an S connection (unlike the gv$ob_session view).

If you can make it through all the examples above, then either you now have a comprehensive understanding of session IDs, or you’re thoroughly dizzy. That’s fine — if you’re dizzy, bookmark this first and come back to read it with a purpose when you hit a problem later.

If you can hang on to this point, then let’s continue and discuss one more command that goes with session ids — kill.

5. Session IDs and the kill Command

kill is followed by a session id; after execution, that session is killed, the executing SQL stops, and the resources previously locked are released.

But because of the inherent complexity of session ids, the implementation of the kill command also has different logic. We generally use a value from the queried ID column as the encoding that follows kill, which may be cs_id or server_sessid.

There are also some characteristics to understand:

1. The session executing kill can, besides killing itself, also kill other sessions.
2. Before ODP version 4.2.3 (hereafter called non-pass-through kill), kill was executed by the proxy itself, directly disconnecting the relevant session’s C connection and S connections from the proxy side.
3. Starting with ODP version 4.2.3 (hereafter called pass-through kill), the kill information is handed to OBServer to execute, then an error code tells the proxy, and the proxy then disconnects the relevant C and S connections.
4. The proxy itself is stateless and can’t see the cs_id in other proxies.
5. cs_id is the proxy’s internal encoding for the C connection; OBServer’s awareness of the C connection is proxy_sessid.

For the non-pass-through kill case, here’s a table:

ID (row) \ Component (col)	proxy (C connection on this proxy)	proxy (C connection not on this proxy)	direct connection (regardless of whether the S connection is on this OBServer)
cs_id	Recognized, can kill.	Not recognized — because the proxy is stateless, can’t kill. Or by coincidence there happens to be an identical cs_id, killing the wrong one.	Not recognized; there’s no cs_id information at all.
server_sessid (primary)	Recognized, can kill.	Recognizable, but not managed by this proxy, can’t kill.	Recognized, kill succeeds; once the proxy perceives it, it subsequently disconnects the corresponding C connection.
server_sessid (non-primary)	Recognized, can kill.	Recognizable, but not managed by this proxy, can’t kill.	Many cases, fairly complex.

First, the recommendation: if you’re using a proxy, don’t use the direct-connection method to kill.

If you run into a kill that won’t go through (possibly because a load balancer like F5 routed the kill statement to another proxy that can’t handle it), just run it a few more times — usually one of them will kill successfully (one success counts as a successful kill). Although the table above notes it’s possible to kill the wrong one, the probability is very low.

Because the non-pass-through case above runs into all sorts of problems, OceanBase R&D later redesigned kill. It mainly solved 2 problems:

1. The problem of a C connection not being managed by the proxy (it’s now uniformly handed to the OBServer behind it to execute, and then the relevant proxy can receive the OBServer’s return code and handle disconnecting the C connection).
2. The problem that cs_id might be duplicated across different proxies (the newly designed client session id is enabled to replace cs_id, made unique through reasonable encoding). In addition, client session id is also told to OBServer when the proxy and OBServer establish a connection, so OBServer recognizes it too.

For the pass-through kill case, here’s a corresponding table as well:

ID (row) \ Component (col)	proxy (C connection on this proxy)	proxy (C connection not on this proxy)	direct connection (regardless of whether the S connection is on this OBServer)
client session id	Recognized, can kill.	Recognizable (just ask OBServer), then passed through to OBServer to execute, can kill. No duplication problem.	Recognized, can kill.
server_sessid (primary)	Recognized, can kill.	Recognizable, passed through, can kill.	Recognized, kill succeeds — the effect is as if the proxy passed the kill through.
server_sessid (non-primary)	Recognized, can kill.	Recognizable, passed through, can kill.	Many cases, fairly complex.

After pass-through, isn’t the situation much better? But my recommendation is still to use the proxy to manage kill (and upgrade both components to the new version that uses client session id). Managing things according to the common case minimizes problems.

It’s worth mentioning that the pass-through table leaves out many cases — did you spot that? The reason is that at this point both the proxy and OBServer have 2 sets of logic for the kill command. The examples here are all new-proxy logic against new-OBServer logic; there are also the cases of old-proxy logic against new-OBServer logic and new-proxy logic against old-OBServer logic.

Ideally we’d list all the cases, but the author of this article, Jinchuan, said he was being lazy and didn’t want to sort out the other cases — so let’s forgive him~

6. Summary

Finally, returning to the question at the beginning of the article: why use so many terms to manage just 2 connection types?

1. The biggest reason is still the complexity introduced by the distributed architecture. And the proxy is designed to be stateless, with a load balancer like F5 added in front, which also increases management difficulty.
2. Database software is highly complex and developed by multiple teams in collaboration, so there are inevitably some things that aren’t unified, along with some redundant designs — otherwise communication costs would be amplified without limit.
3. Database software is a continuously evolving system. Some things, though they now seem useless, can’t be removed and stay in the code long-term as part of compatibility. Just like our DNA — many sequences aren’t expressed and serve no purpose, but they’re part of the human evolutionary process and so are preserved in our DNA.

7. Follow-up

There’s also a code that appears in the proxy logs and refers to the C connection — sm_id — about which there’s relatively little information. sm_id is how the proxy_sm module within the proxy refers to cs_id. It only appears in the logs; since no view in the database can query this information, I believe it’s mainly for use by the proxy_sm module developers. Generally, when troubleshooting logs you can filter by trace_id.