2014年4月24日 20:03:12go评论120阅读模式

英文:

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 &quot;fmt&quot;
import &quot;time&quot;
import &quot;code.google.com/p/go-sqlite/go1/sqlite3&quot;
import &quot;crypto/rand&quot;
import &quot;encoding/base64&quot; 
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(&quot;getrandomtext() : &#39;&quot;+returntext+&quot;&#39;\n&quot;)
return returntext
}
func main() {
dbname := &quot;multitasking.db&quot;
tablename := &quot;multiwrite&quot;
defer time.Sleep(5000 * time.Millisecond)
db, err := sqlite3.Open(&quot;file:&quot;+dbname+&quot;?file:locked.sqlite?cache=shared&amp;mode=rwc&quot;)
defer db.Close()
if err != nil {
fmt.Printf(&quot;failed to open database, error: &quot; + err.Error() + &quot;\n&quot;) 
return
} 
err = db.Exec(&quot;DROP TABLE IF EXISTS &quot;+tablename+&quot;;&quot;)
if err != nil {
fmt.Printf(&quot;error dropping table &quot;+tablename+&quot;: &quot;+err.Error()+&quot;\n&quot;)
}
err = db.Exec(&quot;CREATE TABLE &quot;+tablename+&quot; (id INTEGER PRIMARY KEY AUTOINCREMENT, text VARCHAR(200));&quot;)
if err != nil {
fmt.Printf(&quot;error creating table &quot;+tablename+&quot;: &quot;+err.Error()+&quot;\n&quot;) 
return
} else {
fmt.Printf(&quot;successfully created table &quot;+tablename+&quot;!\n&quot;) 
}
var insertcount int = 128
fmt.Printf(&quot;inserting %d random text rows ...\n&quot;, insertcount) 
var counter int = 0
insertloop:
if counter &lt; insertcount {
counter++
go func(count int) {
if db.Exec(&quot;INSERT INTO &quot;+tablename+&quot;(text) VALUES(\&quot;&quot;+getrandomtext()+&quot;\&quot;);&quot;) !=nil {
fmt.Printf(&quot; -%d&quot;, count)
} else {
fmt.Printf(&quot; +%d&quot;, count)
}
}(counter)
goto insertloop
}
fmt.Printf(&quot;\nExecuted! Waiting some seconds...\n&quot;)
time.Sleep(3000 * time.Millisecond)			
fmt.Printf(&quot;\nRequesting...\n&quot;)
ReadTable, err := db.Prepare(&quot;SELECT id, text FROM &quot;+tablename+&quot;;&quot;)
err = ReadTable.Query()
if err != nil {
fmt.Printf(&quot;failed to read &#39;&quot;+tablename+&quot;&#39; table, error: &quot; + err.Error() + &quot;\n&quot;)
return
} 
Readloop:
var RowId int
var RowText string
err = ReadTable.Scan(&amp;RowId, &amp;RowText)
if err == nil {
fmt.Printf(&quot;&gt; %d | %s\n&quot;, RowId, RowText)
ReadTable.Next() 
goto Readloop
}
fmt.Printf(&quot;Sqlite3 test done! :)\n&quot;)
}

When multitasking.db does not exists all works fine:

C:\Documents and Settings\JekabsR&gt;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...
&gt; 1 | ScnK0DScszFPtNgY
&gt; 2 | 8ALa+Dyk48PpJ4em
&gt; 3 | hmEF4yINhg9SxlNy
...
&gt; 127 | AAAAwNCvV/wd0/MR
&gt; 128 | SEbPfK/XuVfgnxPj
Sqlite3 test done! :)

When i hit multitaskingdb again, this causes panic:

C:\Documents and Settings\JekabsR&gt;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)

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

模拟多个对SQLite数据库的请求作为goroutine会导致随机的panic错误。

问题

答案1

日志显示Lambda已完全运行，但函数以退出状态1退出。

array from form-data in golang return as a string instead of array

在Go泛型中，什么时候不需要使用波浪符（tilde）？

在Go语言中解组异构XML元素列表

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。