Nsryjdtyk

I have some fundamental questions about how database clients & database interact

1. Do databases support multiple transactions simultaneously on a single database connection from client? If not, why not? (as multiplexing would save on resource overhead per connection & connection pools are a source of contention when thousands of simultaneous queries needs to be executed simultaneously, which multiplexing for sure avoids)


2. Whats the relationship between database client's level Connection vs physical raw TCP connection. Is it many-to-one[multuplexing] (or) one-to-one? If not multiplexed why not?


3. If multiplexed, does the database server maintain a single logical connection from its end (or) multiple logical connections

PS: I understand some of these details will vary from database to database, buit want to know in general how popular implementations such as Postgres, Mysql, Oracle, SQL server & DB2 implement these

Do databases support multiple transactions simultaneously on a single database connection from client?

For SQL Server, no.

If not, why not? (as multiplexing would save on resource overhead per connection)

It would seriously complicate the network protocol, which has to be implemented on multiple client platforms, creating a possible source of bugs and performance issues.

And the resource overhead caused by multiple connections is small, and largely mitigated by connection pooling, where a set of long-lived connections is shared among all the threads in a client program.

Oracle

It is a little-known fact, that in Oracle, one can have 0, 1 or even more sessions in the very same TCP connection.

This is discussed in the book Expert Oracle Database Architecture (ISBN 978-1-4302-6299-2, Authors: Kyte, Thomas, Kuhn, Darl) in Chapter 5 - Oracle Processes.

https://books.google.com/books?id=NG4RpD8aLEIC&pg=PA170

Connections vs. Sessions

It surprises many people to discover that a
connection is not synonymous with a session. In most people’s eyes
they are the same, but the reality is they do not have to be. A
connection may have zero, one, or more sessions established on it.
Each session is separate and independent, even though they all share
the same physical connection to the database. A commit in one session
does not affect any other session on that connection. In fact, each
session using that connection could use different user identities! In
Oracle, a connection is simply a physical circuit between your client
process and the database instance — a network connection, most
commonly. The connection may be to a dedicated server process or to a
dispatcher. As previously stated, a connection may have zero or more
sessions, meaning that a connection may exist with no corresponding
sessions.

Demo:

Log in to the database:

[oracle@o71 ~]$ sqlplus bp/bp@'localhost:1521/min18'



SQL*Plus: Release 18.0.0.0.0 - Production on Thu Dec 27 21:20:03 2018

Version 18.4.0.0.0



Copyright (c) 1982, 2018, Oracle.  All rights reserved.



Last Successful login time: Thu Dec 27 2018 21:07:47 +01:00



Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.4.0.0.0



SQL>

In another session, started somewhere else, query the sessions of BP:

SQL> select sid, process, port, paddr from v$session where username = 'BP';



       SID PROCESS                        PORT PADDR

---------- ------------------------ ---------- ----------------

       395 31251                         35298 0000000066E75338

Now enable autotrace in the original session:

SQL> set autotrace on

And the check the sessions again, from the other session:

SQL> select sid, process, port, paddr from v$session where username = 'BP';



       SID PROCESS                        PORT PADDR

---------- ------------------------ ---------- ----------------

       395 31251                         35298 0000000066E75338

       399 31251                         35298 0000000066E75338



SQL> !sudo netstat -tanlp | grep 35298

tcp        0      0 127.0.0.1:35298         127.0.0.1:1521          ESTABLISHED 31251/sqlplus

tcp        0      0 127.0.0.1:1521          127.0.0.1:35298         ESTABLISHED 31253/oracleMIN18

We have 2 sessions, using the same client and server processes and the same TCP connection as well (and that is the usually surprising part). Now if we disconnect, but leave sqlplus running in the first session:

SQL> disconnect

Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.4.0.0.0

SQL>

And check the database again from the other session:

SQL> select sid, process, port, paddr from v$session where username = 'BP';



no rows selected



SQL> select spid from v$process where addr = '0000000066E75338';



SPID

------------------------

31253



SQL> !sudo netstat -tanlp | grep 35298

tcp        0      0 127.0.0.1:35298         127.0.0.1:1521          ESTABLISHED 31251/sqlplus

tcp        0      0 127.0.0.1:1521          127.0.0.1:35298         ESTABLISHED 31253/oracleMIN18



SQL> select sid, process, port, paddr from v$session where paddr = '0000000066E75338';



no rows selected

We still have the database server process, we still have the client process, we still have a TCP connection between them, but we have 0 sessions associated with them. Once you quit sqlplus with exit, that is when the processes and the connection terminate:

SQL> exit

[oracle@o71 ~]$

And:

SQL> select spid from v$process where addr = '0000000066E75338';



no rows selected



SQL> !sudo netstat -tanlp | grep 35298

tcp        0      0 127.0.0.1:35298         127.0.0.1:1521          TIME_WAIT   -

So it is possible, but I have never seen this in practice apart from the above book and demos built based on it.

The parallelism you hint at in Q1 is oversold. Even when you can do things in parallel, the system bogs down for many reasons:

• Hit a brick wall of some resource: CPU / Network / Disk I/O / etc.


• There will be "critical sections" and other interlocks to prevent stepping on each other. For "a few" connections / transactions / etc, this is not a big deal. But even at a few dozen, the system begins to noticeably stumble over itself.


• Some multi-threaded applications hit an algorithm brick wall. Sorting is a classic example. Maybe you can launch a hundred threads (and get nearly a hundred-fold speedup) to compute the items in a big list, but if you need the resultset to be sorted, the application will not be able to get anywhere near the hundred-fold speedup in that phase. And then you have to funnel all the data into a single stream for delivering!

Databases are easier to design if you stop with the necessary requirement: separate clients must not step on each other. Then, within a single client, it is easier to focus on doing one thing at a time.

Learn about KISS.

As for the TCP layer -- You have the opportunity to design a router that can achieve what you suggest. You could make millions. But it belongs at a low level, not in the database engine.