英文:
emulating multiple requests to sqlite database as goroutine causes random panic
问题
问题出在并发访问数据库时,可能会导致空指针解引用错误。在你的代码中,你使用了goroutine来执行数据库操作,但是在并发执行时,可能会导致数据库连接(db
)在某些goroutine中为nil。这可能是由于并发访问数据库连接而引起的竞态条件。
为了解决这个问题,你可以在每个goroutine中创建一个新的数据库连接,而不是共享同一个连接。这样每个goroutine都有自己的数据库连接,就不会出现竞态条件。
另外,你还可以使用互斥锁(mutex)来保护对数据库连接的访问,以确保同一时间只有一个goroutine可以访问数据库连接。
以下是修改后的代码示例:
package main
import (
"fmt"
"time"
"code.google.com/p/go-sqlite/go1/sqlite3"
"crypto/rand"
"encoding/base64"
"sync"
)
func getrandomtext() string {
b := make([]byte, 12)
rand.Read(b)
en := base64.StdEncoding // or URLEncoding
enclen := en.EncodedLen(len(b))
d := make([]byte, enclen)
en.Encode(d, b)
returntext := string(d[:enclen])
return returntext
}
func main() {
dbname := "multitasking.db"
tablename := "multiwrite"
defer time.Sleep(5000 * time.Millisecond)
db, err := sqlite3.Open("file:"+dbname+"?file:locked.sqlite?cache=shared&mode=rwc")
defer db.Close()
if err != nil {
fmt.Printf("failed to open database, error: " + err.Error() + "\n")
return
}
err = db.Exec("DROP TABLE IF EXISTS "+tablename+";")
if err != nil {
fmt.Printf("error dropping table "+tablename+": "+err.Error()+"\n")
}
err = db.Exec("CREATE TABLE "+tablename+" (id INTEGER PRIMARY KEY AUTOINCREMENT, text VARCHAR(200));")
if err != nil {
fmt.Printf("error creating table "+tablename+": "+err.Error()+"\n")
return
} else {
fmt.Printf("successfully created table "+tablename+"!\n")
}
var insertcount int = 128
fmt.Printf("inserting %d random text rows ...\n", insertcount)
var wg sync.WaitGroup
var mu sync.Mutex
for counter := 0; counter < insertcount; counter++ {
wg.Add(1)
go func(count int) {
defer wg.Done()
mu.Lock()
defer mu.Unlock()
newdb, err := sqlite3.Open("file:"+dbname+"?file:locked.sqlite?cache=shared&mode=rwc")
if err != nil {
fmt.Printf("failed to open database, error: " + err.Error() + "\n")
return
}
defer newdb.Close()
if newdb.Exec("INSERT INTO "+tablename+"(text) VALUES('"+getrandomtext()+"');") != nil {
fmt.Printf(" -%d", count)
} else {
fmt.Printf(" +%d", count)
}
}(counter)
}
wg.Wait()
fmt.Printf("\nExecuted! Waiting some seconds...\n")
time.Sleep(3000 * time.Millisecond)
fmt.Printf("\nRequesting...\n")
ReadTable, err := db.Prepare("SELECT id, text FROM "+tablename+";")
err = ReadTable.Query()
if err != nil {
fmt.Printf("failed to read '"+tablename+"' table, error: " + err.Error() + "\n")
return
}
Readloop:
var RowId int
var RowText string
err = ReadTable.Scan(&RowId, &RowText)
if err == nil {
fmt.Printf("> %d | %s\n", RowId, RowText)
ReadTable.Next()
goto Readloop
}
fmt.Printf("Sqlite3 test done! :)\n")
}
这样修改后的代码会为每个goroutine创建一个新的数据库连接,并使用互斥锁来保护对数据库连接的访问,以避免竞态条件。
英文:
I am trying to emulate multiple requests from http.HandleFunc
to function which makes changes to sqlite database. I assume that function which is called by http.HandleFunc
is actually goroutine. See code below:
package main
import "fmt"
import "time"
import "code.google.com/p/go-sqlite/go1/sqlite3"
import "crypto/rand"
import "encoding/base64"
func getrandomtext() (string) {
b := make([]byte, 12)
rand.Read(b)
en := base64.StdEncoding // or URLEncoding
enclen := en.EncodedLen(len(b))
d := make([]byte, enclen)
en.Encode(d, b)
returntext := string(d[:enclen])
//fmt.Printf("getrandomtext() : '"+returntext+"'\n")
return returntext
}
func main() {
dbname := "multitasking.db"
tablename := "multiwrite"
defer time.Sleep(5000 * time.Millisecond)
db, err := sqlite3.Open("file:"+dbname+"?file:locked.sqlite?cache=shared&mode=rwc")
defer db.Close()
if err != nil {
fmt.Printf("failed to open database, error: " + err.Error() + "\n")
return
}
err = db.Exec("DROP TABLE IF EXISTS "+tablename+";")
if err != nil {
fmt.Printf("error dropping table "+tablename+": "+err.Error()+"\n")
}
err = db.Exec("CREATE TABLE "+tablename+" (id INTEGER PRIMARY KEY AUTOINCREMENT, text VARCHAR(200));")
if err != nil {
fmt.Printf("error creating table "+tablename+": "+err.Error()+"\n")
return
} else {
fmt.Printf("successfully created table "+tablename+"!\n")
}
var insertcount int = 128
fmt.Printf("inserting %d random text rows ...\n", insertcount)
var counter int = 0
insertloop:
if counter < insertcount {
counter++
go func(count int) {
if db.Exec("INSERT INTO "+tablename+"(text) VALUES(\""+getrandomtext()+"\");") !=nil {
fmt.Printf(" -%d", count)
} else {
fmt.Printf(" +%d", count)
}
}(counter)
goto insertloop
}
fmt.Printf("\nExecuted! Waiting some seconds...\n")
time.Sleep(3000 * time.Millisecond)
fmt.Printf("\nRequesting...\n")
ReadTable, err := db.Prepare("SELECT id, text FROM "+tablename+";")
err = ReadTable.Query()
if err != nil {
fmt.Printf("failed to read '"+tablename+"' table, error: " + err.Error() + "\n")
return
}
Readloop:
var RowId int
var RowText string
err = ReadTable.Scan(&RowId, &RowText)
if err == nil {
fmt.Printf("> %d | %s\n", RowId, RowText)
ReadTable.Next()
goto Readloop
}
fmt.Printf("Sqlite3 test done! :)\n")
}
When multitasking.db
does not exists all works fine:
C:\Documents and Settings\JekabsR>multitaskingdb
successfully created table multiwrite!
inserting 128 random text rows ...
Executed! Waiting some seconds...
+2 +3 +4 +5 +6 +7 +8 +9 +10 +11 +57 +58 +59 +60 +61 +62 +63 +64 +65 +66 +67 +68
+69 +70 +71 +72 +73 +74 +75 +76 +77 +78 +79 +80 +81 +82 +83 +84 +85 +86 +87 +88
+89 +90 +91 +92 +93 +94 +95 +96 +97 +98 +117 +118 +119 +120 +121 +122 +123 +124
+125 +126 +127 +128 +12 +13 +14 +15 +16 +17 +18 +19 +20 +21 +22 +23 +24 +25 +26
+27 +28 +29 +30 +31 +32 +33 +34 +35 +36 +37 +38 +39 +40 +41 +42 +43 +44 +45 +46
+47 +48 +49 +50 +51 +52 +53 +54 +55 +56 +99 +100 +101 +102 +103 +104 +105 +106
+107 +108 +109 +110 +111 +112 +113 +114 +115 +116 +1
Requesting...
> 1 | ScnK0DScszFPtNgY
> 2 | 8ALa+Dyk48PpJ4em
> 3 | hmEF4yINhg9SxlNy
...
> 127 | AAAAwNCvV/wd0/MR
> 128 | SEbPfK/XuVfgnxPj
Sqlite3 test done! :)
When i hit multitaskingdb
again, this causes panic:
C:\Documents and Settings\JekabsR>multitaskingdb
successfully created table multiwrite!
inserting 128 random text rows ...
Executed! Waiting some seconds...
+2 +3 +4 +5 +6 +7 +8 +9 +10panic: invalid memory address or nil pointer derefer
ence
fatal error: panic during malloc
[signal 0xc0000005 code=0x0 addr=0x20 pc=0x41b60a]
runtime stack:
runtime.panic(panic: invalid memory address or nil pointer dereference
+11 -57 -58 -59 -60 -61 -62 -63 -64 -65 -66 -67 -68 -69 -700x520a80, 0x673aaf)
C:/Program Files/Go/src/pkg/runtime/panic.c:233 +0x2b
invalid spdelta 363589 -1
runtime: unexpected return pc for -71 -72 -73 -74 -75 -76 -77 -78 -79 -80 -81 -
82 -83 -84 -85 -86 -87 -88balance called from 0x200
goroutine 48 [syscall]:
runtime.cgocall(0x492817, 0x314476e8)
C:/Program Files/Go/src/pkg/runtime/cgocall.c -89 -90 -91 -92 -93 -94 -9
5 -96 -97 -98 -99 -100 -101 -102 -103 -104 -105 -106:149 +0x10c fp=0x314476dc
code.google.com/p/go-sqlite/go1/sqlite3._Cfunc_sqlite3_exec(0x3d4258, 0x10f47480
, 0x0, 0x0, 0x0, ...)
C:/DOCUME~1/JekabsR/LOCALS~1/Temp/go-build368528647/code.google.com/p/go
-sqlite/go1/sqlite3/_obj/_cgo_defun.c:456 +0x33 fp=0x314476e8
code.google.com/p/go-sqlite/go1/sqlite3.(*Conn).exec(0x10f2d6c0, 0x10f47480, 0x3
8 +13 +14 +15 +16 +17 +18 +19 +20 +21 +22 +23 +24 +25 +26 +27 +28 +29, 0x52f578)
C:/ProgramFiles/Go/src/pkg/code.google.com/p/go-sqlite/go1/sqlite3/sqlit
e3.go:545 +0x4c fp=0x31447704
code.google.com/p/go-sqlite/go1/sqlite3.(*Conn).Exec(0x10f2d6c0, 0x10f47480, +3
0 +31 +32 +33 +34 +35 +36 +37 +38 +39 +40 +41 +42 +43 +44 +47 -48 -490x39, 0x0,
0x0, ...)
C:/ProgramFiles/Go/src/pkg/code.google.com/p/go-sqlite/go1/sqlite3/sqlit
e3.go:231 +0xd2 fp=0x31447764
main.func┬Ę001( -50 -51 -52 -53 -54 -55 -56 +1 -107 -108 -109 -110 -111 -112 -11
3 -114 -115 -1160x2d)
C:/Program Files/Go/src/pkg/development/multitaskingdb/multitaskingdb.go
:52 +0xa2 fp=0x314477c4
runtime.goexit()
C:/Program Files/Go/src/pkg/runtime/proc.c:1394 -117 -118 -119 -120 -121
-122 -123 -124 -125 -126 -127 -128 +12 fp=0x314477c8
created by main.main
C:/Program Files/Go/src/pkg/development/multitaskingdb/multitaskingdb.go
:57 +0x417
goroutine 1 [sleep]:
time.Sleep(0xb2d05e00, 0x0)
C:/Program Files/Go/src/pkg/runtime/time.goc:31 +0x3b
main.main()
C:/Program Files/Go/src/pkg/development/multitaskingdb/multitaskingdb.go
:61 +0x453
goroutine 3 [syscall]:
runtime.goexit()
C:/Program Files/Go/src/pkg/runtime/proc.c:1394
goroutine 49 [syscall]:
code.google.com/p/go-sqlite/go1/sqlite3._Cfunc_sqlite3_exec(0x3d4258, 0x10f47500
, 0x0, 0x0, 0x0, ...)
C:/DOCUME~1/JekabsR/LOCALS~1/Temp/go-build368528647/code.google.com/p/go
-sqlite/go1/sqlite3/_obj/_cgo_defun.c:456 +0x33
code.google.com/p/go-sqlite/go1/sqlite3.(*Conn).exec(0x10f2d6c0, 0x10f47500, 0x3
8, 0x52f578)
C:/ProgramFiles/Go/src/pkg/code.google.com/p/go-sqlite/go1/sqlite3/sqlit
e3.go:545 +0x4c
code.google.com/p/go-sqlite/go1/sqlite3.(*Conn).Exec(0x10f2d6c0, 0x10f47500, 0x3
9, 0x0, 0x0, ...)
C:/ProgramFiles/Go/src/pkg/code.google.com/p/go-sqlite/go1/sqlite3/sqlit
e3.go:231 +0xd2
main.func┬Ę001(0x2e)
C:/Program Files/Go/src/pkg/development/multitaskingdb/multitaskingdb.go
:52 +0xa2
created by main.main
C:/Program Files/Go/src/pkg/development/multitaskingdb/multitaskingdb.go
:57 +0x417
fatal error: panic during malloc
[signal 0xc0000005 code=0x0 addr=0x20 pc=0x41b60a]
runtime stack:
runtime.panic(0x520a80, 0x673aaf)
C:/Program Files/Go/src/pkg/runtime/panic.c:233 +0x2b
invalid spdelta 364736 -1
runtime: unexpected return pc for sqlite3BtreeInsert called from 0x1500
Where is the problem?
答案1
得分: 3
根据sqlite3包的文档,每个goroutine应该有一个连接,而不是多个goroutine共享一个连接。
根据https://godoc.org/code.google.com/p/go-sqlite/go1/sqlite3的说明:
并发性
一个单独的连接实例及其所有派生对象(如预编译语句、备份操作等)在没有外部同步的情况下不能从多个goroutine并发使用。唯一的例外是Conn.Interrupt(),可以从另一个goroutine中调用它来中断长时间运行的操作。即使访问同一个数据库文件,同时使用单独的连接实例也是安全的。例如:
// 错误的示例(没有额外的同步)
c, _ := sqlite3.Open("sqlite.db")
go use(c)
go use(c)
// 正确的示例
c1, _ := sqlite3.Open("sqlite.db")
c2, _ := sqlite3.Open("sqlite.db")
go use(c1)
go use(c2)
英文:
According to the documentation of the sqlite3 package, you should have one connection per goroutine and not a single connection shared by several goroutines.
From https://godoc.org/code.google.com/p/go-sqlite/go1/sqlite3:
> Concurrency
>
> A single connection instance and all of its derived
> objects (prepared statements, backup operations, etc.) may NOT be used
> concurrently from multiple goroutines without external
> synchronization. The only exception is Conn.Interrupt(), which may be
> called from another goroutine to abort a long-running operation. It is
> safe to use separate connection instances concurrently, even if they
> are accessing the same database file. For example:
>
> // ERROR (without any extra synchronization)
> c, _ := sqlite3.Open("sqlite.db")
> go use(c)
> go use(c)
>
> // OK
> c1, _ := sqlite3.Open("sqlite.db")
> c2, _ := sqlite3.Open("sqlite.db")
> go use(c1)
> go use(c2)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论