Postgresql hang. My screwup?

[Mark Fletcher]

The following test program opens connections to 2 Postgresql databases and then spawns 200 goroutines. Each goroutine runs some Begin/Commit/Rollback commands and exits. For me, the program hangs after a few goroutines are successful. The goroutines are hanging in the initial dbA.Begin() statement.

If I comment out the Commit/Begin pair in the middle, the program runs to completion.

Clearly I'm doing something wrong, but I've been banging my head trying to figure out what it is. Can someone enlighten me? This is Go 1.5, Postgresql 9.3. The program is running on Mac 10.10.5, the databases are on CentOS 6.5.

Any help would be greatly appreciated!

Thanks,

Mark

package main

import (

"database/sql"

"log"

"sync"

_ "github.com/lib/pq"

)

func main() {

dbA, err := sql.Open("postgres", "user=user password=passwd host=127.0.0.1 dbname=db1 sslmode=disable")

if err != nil {

log.Fatal(err)

}

defer dbA.Close()

dbA.SetMaxIdleConns(0)

dbA.SetMaxOpenConns(5)

dbB, err := sql.Open("postgres", "user=user password=passwd host=127.0.0.1 dbname=db2 sslmode=disable")

if err != nil {

log.Fatal(err)

}

defer dbB.Close()

dbB.SetMaxIdleConns(0)

dbB.SetMaxOpenConns(5)

var wg sync.WaitGroup

for i := 0; i < 200; i++ {

wg.Add(1)

go func(id int) {

defer wg.Done()

log.Printf("Start %d", id)

txA, err := dbA.Begin()

log.Printf("Begin A done %d", id)

if err != nil {

log.Fatal(err)

}

txB, err := dbB.Begin()

log.Printf("Begin B done %d", id)

if err != nil {

log.Fatal(err)

}

// If you comment out this Commit and Begin pair, it completes normally.

err = txA.Commit()

log.Printf("Commit A done %d", id)

if err != nil {

log.Fatal(err)

}

txA, err = dbA.Begin()

log.Printf("Begin 2 A done %d", id)

if err != nil {

log.Fatal(err)

}

err = txA.Rollback()

log.Printf("Rollback A done %d", id)

if err != nil {

log.Fatal(err)

}

err = txB.Rollback()

log.Printf("Rollback B done %d", id)

if err != nil {

log.Fatal(err)

}

log.Printf("End %d", id)

}(i)

}

wg.Wait()

}

[James Aguilar]

Isn't it a deadlock on the connection resource? Because you acquire A->B->A? So if all of the A connections and B connections are used when you reach the second block, you will block waiting for a connection to A, and then never release your connection to B. (I'm assuming that each txn takes a connection. I don't know the sql package, but that's what this appears to be to me.)

[James Aguilar]

One way to test my theory would be to reorder the first two Begin() calls so that you always get all dbB transactions before any dbA transactions. If that fixes the problem, then this is the issue.

(Also, it looks like you're trying to do a dependent commit in dbB based on a transaction in dbA. This can be a bad idea, because consistency could be lost if your program crashes between the time that A commits and B is either committed or rolled back.)

[Kiki Sugiaman]

James is correct. Your second call of dbA.Begin() within a single
goroutine happens before txB is rolled back (or committed for that
matter). So it blocks waiting for an available connection.

You might think that a connection is guaranteed to be available because
you just committed txA prior to the call. But there's no guarantee that
the last connection is not immediately taken by a Begin() call from
another goroutine.

Another way is to move dbB's Begin-Rollback pair into a separate
goroutine. Still gotcha prone, but you know what you are trying to do
more than I do.

[Mark Fletcher]

Thank you both for your replies. So what I have is a type of deadlock between the two connection pools. I hadn't thought of that, but yeah, it makes sense. Thank you.

Mark

[Kiki Sugiaman]

You're welcome. I'll make a clarifying note for the people who stumble
upon this thread in the future: it's the "pool" on the pgsql server side
that has been exhausted, and not the client/go side connection pool. The
two client side pools are independent of each other.

> --
> You received this message because you are subscribed to the Google
> Groups "golang-nuts" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to golang-nuts...@googlegroups.com
> <mailto:golang-nuts...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[ma...@joh.to]

On Friday, September 4, 2015 at 12:53:11 AM UTC+2, ksug wrote:

I'll make a clarifying note for the people who stumble
upon this thread in the future: it's the "pool" on the pgsql server side
that has been exhausted, and not the client/go side connection pool.

That doesn't sound accurate.  If you exhaust all the available server connection slots, you get an error when trying to connect.  There is no pool whatsoever on the server's side.
 

The
two client side pools are independent of each other.

But the whole point is that they *are not* in this example.  The program creates a dependency between them and then tries to acquire resources in two different orderings, leading to undetected deadlocks when both pools are out of available connections.


.m

[Kiki Sugiaman]

On 04/09/15 18:35, ma...@joh.to wrote:
>
>
> On Friday, September 4, 2015 at 12:53:11 AM UTC+2, ksug wrote:
>
> I'll make a clarifying note for the people who stumble
> upon this thread in the future: it's the "pool" on the pgsql
> server side
> that has been exhausted, and not the client/go side connection pool.
>
>
> That doesn't sound accurate. If you exhaust all the available server
> connection slots, you get an error when trying to connect. There is
> no pool whatsoever on the server's side.

There isn't. Hence the quotes. I was thinking (erroneously) of maxed
connection number.

> The
> two client side pools are independent of each other.
>
>
> But the whole point is that they *are not* in this example. The
> program creates a dependency between them and then tries to acquire
> resources in two different orderings, leading to undetected deadlocks
> when both pools are out of available connections.
>

You are right. Now that I've had enough sleep, it's easy to see that I
have sent out two posts which contradicted one another. Thanks for the
correction.

>
> .m