sqlite error database is locked when using sqlite3 command line

Question

I use sqlite3 command line, from bash.

I spawn multiple processes, all trying to insert into the same sqlite database file.

I often get "database is locked".

According to my reading of documentation (http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked) , this should never happen: "SQLITE_LOCKED means the source of contention is internal and comes from the same database connection that received the SQLITE_LOCKED error".

sqlite3 command line is single-threaded, so I would expect SQLITE_BUSY in this situation, but not SQLITE_LOCKED.

Code:

doit() {
   sqlite3 /tmp/db "insert into foo(a,b,c) values(1,2,3);
}

doit &; doit &; doit &

I tried adding PRAGMA busy_timeout=2000; and PRAGMA locking_mode=EXCLUSIVE; for the heck of it, but it didn't help.

sqlite3 -version
3.8.9 2015-04-08 12:16:33 8a8ffc862e96f57aa698f93de10dee28e69f6e09

CL. · Accepted Answer · 2015-05-06 17:35:36Z

9

The error message for SQLITE_BUSY is "database is locked"; SQLITE_LOCKED would be "database table is locked".

The following works for me (where no busy_timeout would result in errors):

doit() {
   sqlite3 /tmp/db "pragma busy_timeout=20000; insert into foo(a,b,c) values(1,2,3);"
}

answered May 6, 2015 at 17:35

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dmitry z Over a year ago

Hmm. You are probably right. Call it message confusion. I was now able to achieve the same effect by either: sqlite3 -init <(echo .timeout 20000) or PRAGMA busy_timeout=20000; Do you know if .timeout instruction is equivalent to that pragma?

CL. Over a year ago

.timeout is exactly the same.

Community · Accepted Answer · 2017-04-13 12:42:36Z

5

There's a good answer for this problem on DBA StackExchange. It boils down to running .timeout 1000 at initialization to tell the command line tool to wait 1 second to acquire a lock. I'm not sure if this timeout is the same as the pragma busy_tmeout you reference or if it's different.

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Feb 26, 2016 at 16:30

Nelson

30.1k5 gold badges37 silver badges31 bronze badges

Collectives™ on Stack Overflow

sqlite error database is locked when using sqlite3 command line

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related