4

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

closed as too broad by Sean Gallardy, Mr.Brownstone, Colin 't Hart, Dan Guzman, mustaccio Dec 28 '18 at 17:02

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Note that TCP connections aren't physical... maybe they're close enough from a database system perspective, but an actual physical connection is a wire. – immibis Dec 28 '18 at 4:45
  • Sure, I should have used the word RAW TCP connection – Ashok Koyi Dec 28 '18 at 6:46
  • BTW I feel that TCP protocols of databases are notoriously bad. Most don’t support multiplexing and they typically use blocking reads for the time of operations. This is bad since it ties read timeouts to operation timeouts and it makes reconnects harder. – eckes Dec 28 '18 at 7:00
10

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.

  • Assuming that you have thousands of simultaneous requests, wouldn't multiple requests compete for connections even in the pool? If simultaneous transactions are supported (I understand the complexity, but then non blocking IO is complex but servers do support it for performance reasons), we dont even need to worry about connection contention in pool & the performance should be much better than connection pools – Ashok Koyi Dec 27 '18 at 18:13
  • 6
    A TCP/IP connection is simply not an expensive enough resource to merit this design. The performance would probably not be "much better", and it might be worse as sessions would have to coordinate the utilization of the TCP/IP socket with other sessions wanting to read and write data. – David Browne - Microsoft Dec 27 '18 at 18:37
  • I've heard that postgres has a process/connection => a fixed overhead (~10MB) per connection, even if TCP connection overhead is small. Multiplexing looks to me like an attractive proposition there. Are there any downsides of multiplexing in that case? – Ashok Koyi Dec 27 '18 at 18:39
  • Right, but the costly thing is a session, not a network TCP/IP connection. Connection Pooling, and Application Server thread pools both enable "multiplexing", where 1000s of application users can share 10s of database sessions. – David Browne - Microsoft Dec 27 '18 at 18:43
  • 1
    @AshokKoyi, you can read about the SQL Server TDS session level protocol details here but learning about the costs involved is an exercise for the reader. – Dan Guzman Dec 28 '18 at 13:21
5

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.

3

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.

  • You are making multiple assumptions about the usecases here. Everything you said is equally applicable whether the connection is multiplexed (or) not. I'm talking from the point of view of fundamentals of what an ideal database client should look like. The resource utilisation is there whether you have multiplexed/non multiplexed transactions. But the non-multiplexed are always costly as each connection has a fixed overhead (incase of postgres a process/connection=~10MB space). Non blocking IO does not follow KISS (as its quite complex), yet all performance critical applications use it – Ashok Koyi Dec 27 '18 at 18:37

Not the answer you're looking for? Browse other questions tagged or ask your own question.