Getting database lock error on sqlite3 golang?

Question

my queries

var m sync.Mutex
m.Lock()
defer m.Unlock()

_, err1 := database.Exec(`UPDATE  UserData SET coins = coins + ? WHERE rollno = ?`, transfer_data.Coins, transfer_data.ReceiverRollno)    
if err1 != nil {
   //if some error rollback databse to initial condition and print the error 
   fmt.Println("error lies in database.Exec() err1")
   fmt.Println(err1)
   tx.Rollback()
   return
   // panic(err)
}
    
_, err2 := database.Exec(`UPDATE UserData SET coins = coins - ?  WHERE rollno = ? AND coins - ? >= 0`, transfer_data.Coins, claims.Rollno, transfer_data.Coins)
    
if err2 != nil {
   //if some error rollback databse to initial condition and print the error
   fmt.Println(err2)
   fmt.Println("error lies in database.Exec() err2")
   tx.Rollback()
   return
}

//we are here so this means transaction is successful so Commit this change to the database
tx.Commit()

my table

UserCoin_info := `CREATE TABLE IF NOT EXISTS UserData(
   "rollno" INTEGER NOT NULL,
   "coins" INTEGER NOT NULL
);`
    
statement, err := db.Prepare(UserCoin_info)
if err != nil {
   panic(err)
}
statement.Exec()

I am getting database lock error as err1 while making a update in the first query, i tried using mutexes but still getting this error, i searched for possible solutions but didn't get any.

It would be helpful if you provided the original error message — zerkms, Jun 29 '21 at 04:49
i am getting this error lies in database.Exec() err1 database is locked — Dhruv singhal, Jun 29 '21 at 04:50
`tx, err := database.Begin() if err != nil { fmt.Println("error lies in database.begin()") return } ` — Dhruv singhal, Jun 29 '21 at 04:56
If you run it on windows - it looks some other process has obtained the lock over the db file. — zerkms, Jun 29 '21 at 04:56
`2021/06/29 10:24:07 http: panic serving [::1]:42944: database is locked goroutine 18 [running]: net/http.(*conn).serve.func1(0xc000128000) /usr/lib/go-1.13/src/net/http/server.go:1767 +0x139 panic(0x892300, 0xc000134930)` — Dhruv singhal, Jun 29 '21 at 04:58
complete github code -: (https://github.com/dhruv354/iitk-coin.git) — Dhruv singhal, Jun 29 '21 at 05:01
I removed the mutex and it works , can you please tell me the reason for it — Dhruv singhal, Jun 29 '21 at 05:20
it is better to post the exact error message. In general, i advise you to not close/open your DB at every request, and as mentioned in below answer, always close the query resource once you consumed it. — , Jul 26 '21 at 09:31

Anderson F. Viana · Answer 1 · 2021-07-26T05:18:35.447

0

Database lock is result of not closing *Stmt and *Rows.

statement, err := db.Prepare(UserCoin_info)
if err != nil {
     panic(err)
}
defer statement.Close()

statement.Exec()

edited Jul 26 '21 at 05:18

answered Jul 26 '21 at 05:13

Anderson F. Viana

56
1
4

to read also https://stackoverflow.com/a/32483180/4466350 and this is another variation https://stackoverflow.com/a/37769626/4466350 – Jul 26 '21 at 09:53

Getting database lock error on sqlite3 golang?

1 Answers1