c# - "Database is locked" exception when using Massive with SQLite -

recently browse micro orms , massive sqlite because simple. have issue.

i running select statement followed update statement getting exception. below code :

 var tbl = new cust();             var customers = tbl.all(where: "customerid > @0", orderby: "firstname", columns: "customerid,firstname", args: 4);             var firstcustomername= customers.first().firstname;              var c = tbl.update(new { firstname = "updated2" }, 4); //exception here!              //same happens when using object             //var tbl2 = new cust();             //tbl2.update(new { firstname = "updatedname" }, 4);//exception here! 

the exception message : "database locked", @ method below in massive.sqlite source

public virtual int execute(ienumerable<dbcommand> commands) {        var result = 0;             using (var conn = openconnection())             {                 using (var tx = conn.begintransaction())                 {                     foreach (var cmd in commands)                     {                         cmd.connection = conn;                         cmd.transaction = tx;                         result += cmd.executenonquery();                     }                     tx.commit();//here exception!                 }             }             return result;      } 

when @ massive.sqlite source see massive never closes connections instead relays on using statement dispose connection object, can see in above code.

openconnection() in above code method returns new connection every time called.

 public virtual dbconnection openconnection()  {             var result = _factory.createconnection();             result.connectionstring = connectionstring;             result.open();             return result;  } 

if case massive not closing connection, , according this question sqlite not @ concurrent connections , supposed close it, how can close then? - connection not exposed me.

i want hear best practice developers using massive sqlite.

sqlite likes have single opened connection.

massive managing connections correctly leaves executereader "open" in query method can cause troubles:

robert simpson wrote:

leaving readers open cause issues. won't cleaned until lazy garbage collector gets around it. it'd better in case have using() statements around readers @ least. following objects use unmanaged resources garbage collector lazy cleaning up:

sqlitecommand, sqliteconnection, sqlitedatareader, , possibly sqlitetransaction if recall correctly.

so put using around executereader() in query method , should work fine:

public virtual ienumerable<dynamic> query(string sql, params object[] args) {     using (var conn = openconnection())     {         using (var rdr = createcommand(sql, conn, args).executereader())         {             while (rdr.read())             {                 yield return rdr.recordtoexpando(); ;             }         }     } } 

some notes , other workarounds not requires changing massive source:

• you can enable connection pooling in sqlite pooling setting:

  connectionstring="data source=test.db;version=3;pooling=true;max pool size=100;" 

• the query works right if reads all data reader. used first() combined yield return left reader open. if evaluate query toarray() work:

  var firstcustomername= customers.toarray().first().firstname;