Gorm gorm deadlock may occur while initialization config 'SkipDefaultTransaction' set to false(default) and 'PrepareStmt' set to true

Prerequisites: 'PrepareStmt' been set to true while initializing gorm

1. when 'SkipDefaultTransaction' not set or set to false, gorm will add a transaction for non-query operations automatically,

func BeginTransaction(db *gorm.DB) {
    if !db.Config.SkipDefaultTransaction {
        if tx := db.Begin(); tx.Error == nil {
            db.Statement.ConnPool = tx.Statement.ConnPool
            db.InstanceSet("gorm:started_transaction", true)
        } else if tx.Error == gorm.ErrInvalidTransaction {
                ...

and it will get a connection, start transaction, and then exec PreparedStmtDB.ExecContext, in PreparedStmtDB.ExecContext, gorm will try to get PreparedStmtDB.Mux and then exec the SQL

0x55972dbfc7c8  sync.runtime_SemacquireMutex+0x48
0x55972e1e70a9  sync.(*RWMutex).RLock+0x459
0x55972e1e6c73  gorm.io/gorm.(*PreparedStmtDB).prepare+0x23
0x55972e1e78d2  gorm.io/gorm.(*PreparedStmtTX).ExecContext+0x92
0x55972e23fb9c  gorm.io/gorm/callbacks.Create.func1+0x37c
0x55972e1d97b5  gorm.io/gorm.(*processor).Execute+0x225
0x55972e1de18a  gorm.io/gorm.(*DB).Create+0xba

1. for query operation, gorm does not start transaction, and in PreparedStmtDB.QueryContext->PreparedStmtDB.prepare, it will get the PreparedStmtDB.Mux and then try to get a connection(stmt, err := conn.PrepareContext(ctx, query))

0x55972e191c3d  database/sql.(*DB).conn+0x7ad
0x55972e193009  database/sql.(*DB).prepare+0x59
0x55972e192dca  database/sql.(*DB).PrepareContext+0x9a
0x55972e1e6e70  gorm.io/gorm.(*PreparedStmtDB).prepare+0x220
0x55972e1e74bc  gorm.io/gorm.(*PreparedStmtDB).QueryContext+0x8c
0x55972e2379ea  gorm.io/gorm/callbacks.Query+0xfa
0x55972e1d97b5  gorm.io/gorm.(*processor).Execute+0x225
0x55972e1df12d  gorm.io/gorm.(*DB).Take+0xcd
...

so when all the connections in pool are exhausted, scenario 1 waitting for mutex while holding all connections, and scenario 2 waiting for connection while holding mutex, then deadlock occurs

Comment From: github-actions[bot]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 2 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

Comment From: ddska

I faced the same issue. If PrepareStmt set to true, deadlock occur sometimes.

There is no guaranteed way to reproduce this, but it happens after a heavy load. It does not happen when PrepareStmt set to false.

I can see a lot of goroutines sitting and waiting to acquire a lock:

goroutine 207661 [semacquire, 2775 minutes]:
sync.runtime_SemacquireMutex(0xc000844ad4, 0x403700, 0x0)
    /usr/lib/go-1.16/src/runtime/sema.go:71 +0x47
sync.(*RWMutex).RLock(...)
    /usr/lib/go-1.16/src/sync/rwmutex.go:63
gorm.io/gorm.(*PreparedStmtDB).prepare(0xc000811240, 0x1698798, 0xc0000b0000, 0x16988e8, 0xc00084e5b0, 0xc00188f800, 0xc0057e84e0, 0x163, 0x0, 0xc005cd6100, ...)
    /home/dds/dev/go/pkg/mod/gorm.io/gorm@v1.21.12/prepare_stmt.go:46 +0x4ce
gorm.io/gorm.(*PreparedStmtDB).QueryContext(0xc000811240, 0x1698798, 0xc0000b0000, 0xc0057e84e0, 0x163, 0xc00263b200, 0x1, 0x8, 0xc001452c18, 0x493e66, ...)
    /home/dds/dev/go/pkg/mod/gorm.io/gorm@v1.21.12/prepare_stmt.go:95 +0x8b
gorm.io/gorm/callbacks.RowQuery(0xc00557e030)
    /home/dds/dev/go/pkg/mod/gorm.io/gorm@v1.21.12/callbacks/row.go:14 +0x21a
gorm.io/gorm.(*processor).Execute(0xc00084d360, 0xc00557e030, 0x14afc44)
    /home/dds/dev/go/pkg/mod/gorm.io/gorm@v1.21.12/callbacks.go:130 +0x3f5
gorm.io/gorm.(*DB).Rows(0xc00557e030, 0x127b6c0, 0x16579d8, 0xc002cac3c0)
    /home/dds/dev/go/pkg/mod/gorm.io/gorm@v1.21.12/finisher_api.go:440 +0xc5

while others just waiting to get a connection

goroutine 208325 [select, 2775 minutes]:
database/sql.(*DB).conn(0xc00084e5b0, 0x1698798, 0xc0000b0000, 0x1, 0x0, 0x0, 0x0)
    /usr/lib/go-1.16/src/database/sql/sql.go:1249 +0x845
database/sql.(*DB).begin(0xc00084e5b0, 0x1698798, 0xc0000b0000, 0x0, 0x1, 0x0, 0x0, 0x0)
    /usr/lib/go-1.16/src/database/sql/sql.go:1752 +0x4f
database/sql.(*DB).BeginTx(0xc00084e5b0, 0x1698798, 0xc0000b0000, 0x0, 0xc00084e5b0, 0x40bb01, 0x1303aa0)
    /usr/lib/go-1.16/src/database/sql/sql.go:1734 +0x8f
gorm.io/gorm.(*PreparedStmtDB).BeginTx(0xc000811240, 0x1698798, 0xc0000b0000, 0x0, 0xc000811240, 0xe6ea01, 0xc0008184a0, 0xc000880420)
    /home/dds/dev/go/pkg/mod/gorm.io/gorm@v1.21.12/prepare_stmt.go:74 +0x85
gorm.io/gorm.(*DB).Begin(0xc000880420, 0x0, 0x0, 0x0, 0xc000893288)
    /home/dds/dev/go/pkg/mod/gorm.io/gorm@v1.21.12/finisher_api.go:573 +0x24f

gorm version is v1.21.12.

@jinzhu could you comment on this?

Comment From: choyri

I think I have the same problem. But my SkipDefaultTransaction and PrepareStmt are true. gorm v1.23.5

Screenshot