1

As per my understanding, database connection pool usually works this way:

  1. create n connections during app initialization and put them into a cache(e.g. a list)
  2. a thread will require a connection to do some operation to the db and return the connection back when it has finished
  3. when there is no available connection in the cache, the thread in step2 will be waiting util a connection is pushed back to the cache

My question is:

Can we execute multiple db operations through one connection after we acquire it from the pool instead of do one db operation then put it back? it's seems more efficient, because it saves the time acquiring and putting back the connection. (under multiple threads condition, there must be some cost of locking when add and get from the connection pool)

can anyone help? Thks!

Liu Wenzhe
  • 881
  • 2
  • 8
  • 22
  • 1. It is a TCP *connection*, of which the socket is an *endpoint.* 2. No. 3. See (2). The time 'acquiring and putting back the connection` is insignificant compared to the cost of creating a new connection. Which is why connection pools exist in the first place. – user207421 Jul 15 '19 at 09:49
  • @user207421 I didn't say not use connection pool, what I focus on is that once we acquire a connection from the pool, we can do a lot of db operations through it instead of do one db operation before put it back – Liu Wenzhe Jul 15 '19 at 09:53
  • since it's a TCP connection, why can't multiple requests be send through one TCP connection @user207421 – Liu Wenzhe Jul 15 '19 at 10:02
  • When you ask questions about concurrent usage of connections, and overhead of using connection pools, you are implicitly asking about not using connection pools: and if that isn't what you're really asking, it is unclear what it is that you are asking that hasn't already been answered. For concurrent usage of a single TCP connection you need a concurrent application protocol, and there is no evidence that any databases actually support it, or how it would work usefully in say the case of long result sets. And, again, this is just more evidence that you are trying not to use a connection pool. – user207421 Jul 15 '19 at 10:09
  • I'm discussing about a better usage of connection pool. @user207421. First, If I use one connection under multiple threads condition, it's ok. But it's will be slower than using connection pool under high concurrent requests. So the question two is yes, right? Second, I can acquire a connection from the pool and hold. Then multiple threads send db operations through the connection (like I have 5 connections in connection pool, and there are 50 requests, 10 requests per connection). Is it better than 10 times "acquire and do db operation and put back" operation – Liu Wenzhe Jul 15 '19 at 10:36
  • ok, I get you point@user207421, thks – Liu Wenzhe Jul 15 '19 at 14:26
  • To restore my comment, 'f I use one connection under multiple threads condition, it's ok': no, it isn't..'But it will be slower than using connection pool under high concurrent requests': so why are you even considering it? 'So the question two is yes, right?': wrong, it is still no. 'Then multiple threads send db operations through the connection': this is impossible. 'Is it better than 10 times "acquire and do db operation and put back" operation?': it isn't better than anything, because it doesn't work. Try it before you debate this further. – user207421 Jul 16 '19 at 02:05

1 Answers1

3

Yes, the database connection can be used for multiple operations each time it is acquired from the pool, and this behavior is typical for database applications that use pooling. For example, a connection might be acquired once and reused for several operations during the handling of a request to a REST service. This lifecycle also often involves managing those operations as a single transaction in the database.

erickson
  • 265,237
  • 58
  • 395
  • 493
  • get it, Thks. Another question: if n threads want to do n db operations(e.g. 10 threads do 10 insert op) through one connection, there must be a lock to let them do in linear, right? – Liu Wenzhe Jul 16 '19 at 02:13
  • @LiuWenzhe There are lock-free algorithms that might be used, but there needs to be some sort of concurrency management. In most databases used today, the database can manage locking automatically for many applications. – erickson Jul 16 '19 at 02:21
  • Yes, in the db side, it has some lock algorithms to ensure operation done correctly. But in the client side, it's like this pseudocode: ``` func insert(data) { lock() res = socket.send(data) unlock() } ``` Am I right? – Liu Wenzhe Jul 16 '19 at 02:42
  • @LiuWenzhe An application or DB client library *could* be written that way, but I wouldn’t expect it to be so generally. A more common and useful pattern is to acquire, use, and release a connection by only one thread at a time. There might be some locking around the pool acquire and release operations, but not with each call over the connection; since only one thread can access it at a time, it would be useless. – erickson Jul 16 '19 at 02:51