LuaSQL SQLite and LSP Considerations

This tutorial explains how to avoid SQLite BUSY errors when using SQLite from Lua Server Pages (LSP). SQLite does not prevent an application from opening several write-capable connections, but if multiple connections try to write at the same time, one of them may be unable to acquire the required lock and the operation may fail with BUSY. In a BAS, Mako Server, or Xedge application, LSP pages may run in separate request threads, so write-heavy applications should manage write access deliberately.

For simple request-local work, the standard pattern is to open a short-lived connection, perform the SQL operation, and close the connection before the request ends:

local env = luasql.sqlite()
local conn = assert(env:connect("My-DB-NAME.db"))

-- Perform SQL commands.

conn:close()
env:close()

The connection object is only kept open during the request. Unlike an LSP page, which is automatically closed at the end of the request/response cycle, database resources must be explicitly closed. Close any cursor first, then close the connection and environment. See Command (Request/Response) Environment for an introduction to the LSP environment and life cycle.

Short-lived request connections work well for reads. They are also acceptable for small applications with light write activity, but several write-capable connections can produce SQLite BUSY errors when write requests overlap. Forgetting to close a write connection can also leave the database locked until Lua's garbage collector releases the connection object.

Why a persistent connection needs a dedicated thread

It may be tempting to create one persistent database connection and reuse it from all LSP pages. Do not do this directly. SQLite is a multithreaded database, but an open connection object must only be used by the thread that owns it. Two or more threads using the same connection object may corrupt the connection or database, and may even crash the server.

LSP pages may run in separate threads. One LSP page may also serve concurrent requests. For this reason, if you use a persistent SQLite write connection, keep it owned by one dedicated DB thread and queue writes to that thread. An introduction to BAS threading can be found in Thread Mapping and Coroutines.

Note: the busy error code returned by the SQLite Lua bindings is the string "BUSY", and this error code comes from the SQLite error SQLITE_BUSY. See the SQLite transaction management introduction for more information.

Recommended LSP pattern

The following pattern is recommended for applications that should avoid BUSY errors caused by competing write connections:

Create one ba.thread.create() writer thread per SQLite database.
Open one persistent write connection in startup code such as .preload.
Use conn:setautocommit("IMMEDIATE") on the persistent write connection.
Queue write operations that should avoid competing writer connections to the writer thread.
Use short-lived request-local connections for reads, and close cursors, connections, and environments promptly.
Avoid independent write connections from LSP pages, REST handlers, timers, or other request callbacks unless the application explicitly handles BUSY retries and cleanup.

The following simplified startup code creates a persistent writer connection and a helper function that queues write operations. Production applications should keep this helper in .preload or in a Lua module loaded by .preload.

local env = assert(luasql.sqlite())
local conn = assert(env:connect("My-DB-NAME.db"))
conn:setbusytimeout(2000)
assert(conn:setautocommit("IMMEDIATE"))

local dbthread = ba.thread.create()

function dbwrite(doit)
   dbthread:run(function()
      local ok, err = doit(conn)
      if ok then
         ok, err = conn:commit("IMMEDIATE")
      end
      if not ok then
         trace("SQLite write failed", err)
      end
   end)
end

An LSP page can then delegate a write to the DB thread:

<?lsp

dbwrite(function(conn)
   return conn:execute("INSERT INTO list (element) VALUES ('hello')")
end)

?>

The callback runs in the context of the DB thread, not in the context of the LSP request thread. The thread library queues write requests and executes them in order, ensuring that only one thread operates on the persistent write connection and reducing lock contention from multiple write-capable connections.

Read operations

Reads may use short-lived connections opened directly by the LSP page. This keeps reads fast and avoids sending every read through the writer queue. Always close the cursor, connection, and environment when the read is complete.

local env = assert(luasql.sqlite())
local conn = assert(env:connect("My-DB-NAME.db", "READONLY"))
local cur = assert(conn:execute("SELECT element FROM list"))

local row = cur:fetch({}, "a")
while row do
   -- Use row.element.
   row = cur:fetch(row, "a")
end

cur:close()
conn:close()
env:close()

This read/write split keeps the write path serialized by application design while allowing short-lived read connections. A reader can still see BUSY if it runs at the same time as a write, so production read helpers should set a busy timeout and handle BUSY according to the needs of the application.

Additional SQLite design patterns

The BAS SQLite Dedicated Writer Skill includes additional SQLite design patterns for both humans and AI assistants. Use the skill when designing production BAS, Mako Server, Xedge, or LSP applications that write to SQLite. It expands on the dedicated-writer pattern, request handling, schema setup, failure handling, verification, and common anti-patterns.

Example application

The example consists of a .preload script that opens a persistent write connection when the server starts. This connection is owned by the dedicated DB thread. The example includes an index.lsp page that opens its own read connection for HTTP GET requests. The page also includes an HTML form; when the browser submits data with POST, the LSP page delegates the insert operation to the DB thread.

A ready-to-use example can be downloaded from GitHub.

We tested the application in auto post mode by having ten browser tabs in two different browsers, a total of 20 concurrent requests. The application prints data to the trace buffer which is echoed to the console window. The application is designed to recover from occasional BUSY messages and complete all DB operations. See the .preload and index.lsp code for details.