Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

database/sql: deadlock if not enough connections available #16873

Closed
kayaklee opened this issue Aug 25, 2016 · 17 comments
Closed

database/sql: deadlock if not enough connections available #16873

kayaklee opened this issue Aug 25, 2016 · 17 comments
Milestone

Comments

@kayaklee
Copy link

@kayaklee kayaklee commented Aug 25, 2016

Please answer these questions before submitting your issue. Thanks!

  1. What version of Go are you using (go version)?
    1.5.1
  2. What operating system and processor architecture are you using (go env)?
    GOARCH="amd64"
    GOBIN=""
    GOEXE=""
    GOHOSTARCH="amd64"
    GOHOSTOS="linux"
    GOOS="linux"
    GOPATH="/home/yubai/go:/home/yubai/dev/s3/swiftmeter/:/home/yubai/dev/s3/imageserver/"
    GORACE=""
    GOROOT="/usr/lib/golang"
    GOTOOLDIR="/usr/lib/golang/pkg/tool/linux_amd64"
    GO15VENDOREXPERIMENT=""
    CC="gcc"
    GOGCCFLAGS="-fPIC -m64 -pthread -fmessage-length=0"
    CXX="g++"
    CGO_ENABLED="1"
  3. What did you do?
    If possible, provide a recipe for reproducing the error.
    A complete runnable program is good.
    A link on play.golang.org is best.
package main

import (
  "database/sql"
  "fmt"
  _ "s3lib/third/mysql"
  "os"
)

const (
  User         string = "****"
  Password     string = "****"
  Host         string = "****"
  Port         int    = ****
  ReadTimeout  string = "60s"
  WriteTimeout string = "10s"

  StmtInsertSQL string = "insert into t1 values (?, ?, ?);"
)

func main() {
  dataSource := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8&interpolateParams=false&readTimeout=%s&writeTimeout=%s",
    User, Password, Host, Port, "", ReadTimeout, WriteTimeout)
  db, err := sql.Open("mysql", dataSource)
  if err != nil {
    os.Exit(-1)
  }
  db.SetMaxOpenConns(1)
  db.SetMaxIdleConns(1)

  tx, err := db.Begin()
  if err != nil {
    os.Exit(-1)
  }
  insert_stmt, err := db.Prepare(StmtInsertSQL) // deadlock here
  if err != nil {
    os.Exit(-1)
  }
  insert_stmt_r1 := tx.Stmt(insert_stmt)
  insert_stmt_r1.Exec(10000, "hello", 100)
  tx.Commit()
}
  1. What did you expect to see?
    success or got an "deadlock error"
  2. What did you see instead?
    The process deadlock while calling "Prepare", it seems that, "Prepare" need an idle connection, but I have set the MaxOpenConns as 1, the transaction has occupied the only one connection so the Prepare deadlock.
    It can be worked around by setting MaxOpenConns greater than 1.

The deadlock call stack is:
···
1 @ 0x42dbe3 0x42dca4 0x404ed1 0x40458b 0x4663e6 0x46746e 0x4672d6 0x4014ab 0x42d800 0x45e2b1
0x4663e6 database/sql.(_DB).conn+0x2f6 /usr/lib/golang/src/database/sql/sql.go:704
0x46746e database/sql.(_DB).prepare+0x4e /usr/lib/golang/src/database/sql/sql.go:867
0x4672d6 database/sql.(*DB).Prepare+0x76 /usr/lib/golang/src/database/sql/sql.go:849
0x4014ab main.main+0x4ab /home/yubai/dev/lks3plus/gocode/src/apps/test1/test1.go:33
0x42d800 runtime.main+0x2b0 /usr/lib/golang/src/runtime/proc.go:111
···

@davecheney
Copy link
Contributor

@davecheney davecheney commented Aug 25, 2016

Please check all your errors.

@kayaklee
Copy link
Author

@kayaklee kayaklee commented Aug 25, 2016

Checked, there is not err

@kshvakov
Copy link

@kshvakov kshvakov commented Aug 25, 2016

@kayaklee

db.Prepare(StmtInsertSQL) -- trying to get a new connection

use

tx.Prepare(StmtInsertSQL) 

otherwise prepare and execute can be performed in different connects

@davecheney
Copy link
Contributor

@davecheney davecheney commented Aug 25, 2016

Please check all your errors before submitting an issue. We cannot accept issue without correct error handling.

  db, _ := sql.Open("mysql", dataSource) // missing error check
  db.SetMaxOpenConns(1)
  db.SetMaxIdleConns(1)

  tx, _ := db.Begin() // missing error check
  insert_stmt, _ := db.Prepare(StmtInsertSQL) // missing error check
  insert_stmt_r1 := tx.Stmt(insert_stmt) 
  insert_stmt_r1.Exec(10000, "hello", 100)
  tx.Commit() // missing error check
@kayaklee
Copy link
Author

@kayaklee kayaklee commented Aug 25, 2016

@kshvakov This code is just an example, in mysql project, there is a global prepared-statement store, and it is lazy to prepare statement, so when I begin a transaction, an require an stmt from the global store, it maybe prepare the statement immediately. When I got a stmt from the global cache, I will call tx.Stmt to get a stmt in the transaction. So your answer does not work.

@kayaklee
Copy link
Author

@kayaklee kayaklee commented Aug 25, 2016

@davecheney My code is just an example for you to make the bug reappear, I have modified the code, add error check for Open and Begin. And there is not error, the code will deadlock while calling Prepare. So the error check for Prepare and Commit is not necessary.

@ianlancetaylor ianlancetaylor changed the title sql deadlock database/sql: deadlock if not enough connections available Aug 25, 2016
@ianlancetaylor
Copy link
Contributor

@ianlancetaylor ianlancetaylor commented Aug 25, 2016

If I understand this correctly, I'm not clear on why this is a bug. You have set the maximum number of connections, and you are trying to use more connections. What do you suggest that the database/sql package should do?

@kayaklee
Copy link
Author

@kayaklee kayaklee commented Aug 26, 2016

@ianlancetaylor success or return an error with "deadlock", I do not think deadlock is a correct result.

@ianlancetaylor
Copy link
Contributor

@ianlancetaylor ianlancetaylor commented Aug 26, 2016

I don't see how the program could detect this case in order to return an error. It is possible that the other connection is being used by a different goroutine, and that that goroutine will finish after ten minutes.

@noblehng
Copy link

@noblehng noblehng commented Aug 31, 2016

@kayaklee, did you try with Go newer than 1.5.1? This problem seem to be fixed with commit 6de40099, which was already in Go 1.6.

@kayaklee
Copy link
Author

@kayaklee kayaklee commented Aug 31, 2016

@noblehng thanks, I have check the problem with Go1.7, but it is not fixed

@kshvakov
Copy link

@kshvakov kshvakov commented Aug 31, 2016

@kayaklee
step by step

  db, err := sql.Open("mysql", dataSource) -- not open new connect 

  db.SetMaxOpenConns(1) -- set max open connects to 1

  tx, err := db.Begin() -- open new connect (max open - 1 = 0 free connects)

  insert_stmt, err := db.Prepare(StmtInsertSQL) // deadlock here -- 
-- db.Prepare - try to get a new connect (all connects (1) is busy, we can't return an error because we expect that someone will release connect

  insert_stmt_r1 := tx.Stmt(insert_stmt)
  insert_stmt_r1.Exec(10000, "hello", 100)
  tx.Commit() -- release  connect 
@noblehng
Copy link

@noblehng noblehng commented Aug 31, 2016

@kayaklee, oh. I see that commit doesn't fix this after looking at the code. It's blocked here when trying to acquire a connection from pool. A select with a context.Done() channel could help here, that is a timeout for waiting a connection from connection pool. And I think a timeout error is a standard behavior when the connection pool is full and busy in other database drivers.

@noblehng
Copy link

@noblehng noblehng commented Aug 31, 2016

This should be the same as #13327.

Other database drivers either have settings for a timeout and/or the max number of pending connection requests, or have a nowait mode which return a connection full error immediately.

@quentinmit quentinmit added this to the Go1.8Maybe milestone Sep 6, 2016
@kardianos
Copy link
Contributor

@kardianos kardianos commented Oct 6, 2016

This should be closed as resolved. go1.8 includes ability to QueryContext that will wait on it when acquiring a conn from the pool.

@bradfitz bradfitz closed this Oct 6, 2016
@base698
Copy link

@base698 base698 commented Apr 18, 2017

i'm using go 1.8 and 1.8.1 and I see the behavior described.

@kardianos
Copy link
Contributor

@kardianos kardianos commented Apr 18, 2017

@base698 Please open a new issue, describe the exact situation you are seeing, show code if possible. Lots of things were said here and I'm unsure which part you are referring to.

Closed issues aren't tracked.

@golang golang locked and limited conversation to collaborators Apr 18, 2018
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
10 participants
You can’t perform that action at this time.