Re: [sqlite] reading a row that has been deleted
Keith, For normal operations the writer will wait until the reading is done. But I have a client that is remote and is very slow and could sit on a select statement indefinitly. In this case I would need to create a temp table. Thanks, -Alex On Mon, Jun 2, 2008 at 11:53 AM, Keith Goodman <[EMAIL PROTECTED]> wrote: > On Mon, Jun 2, 2008 at 8:12 AM, Alex Katebi <[EMAIL PROTECTED]> wrote: > > Hi Keith, > > > > Your observation is correct. I did not know that when selecting a table > a > > shared lock is aquired by the reader and writes are locked out until the > > last row is read or stmt is finialized. This is true even for in-memory > > database. > > > > One cure for this problem is to create a temorary table based on the > > result set of the select statement. Then this temp table can be read > without > > locking out writers from the original table. > > > > CREATE TABLE t1select AS SELECT * FROM t1; > > How about keep trying to write until the database is not busy? Would > that work? Then you only have one copy of the data. > > Creating the temp table may be faster than a fancy select statement > but the problem, while less frequent, still remains (reading while the > db is locked for writing). > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] reading a row that has been deleted
On Mon, Jun 2, 2008 at 8:12 AM, Alex Katebi <[EMAIL PROTECTED]> wrote: > Hi Keith, > > Your observation is correct. I did not know that when selecting a table a > shared lock is aquired by the reader and writes are locked out until the > last row is read or stmt is finialized. This is true even for in-memory > database. > > One cure for this problem is to create a temorary table based on the > result set of the select statement. Then this temp table can be read without > locking out writers from the original table. > > CREATE TABLE t1select AS SELECT * FROM t1; How about keep trying to write until the database is not busy? Would that work? Then you only have one copy of the data. Creating the temp table may be faster than a fancy select statement but the problem, while less frequent, still remains (reading while the db is locked for writing). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] reading a row that has been deleted
Hi Keith, Your observation is correct. I did not know that when selecting a table a shared lock is aquired by the reader and writes are locked out until the last row is read or stmt is finialized. This is true even for in-memory database. One cure for this problem is to create a temorary table based on the result set of the select statement. Then this temp table can be read without locking out writers from the original table. CREATE TABLE t1select AS SELECT * FROM t1; Thanks, -Alex On Sun, Jun 1, 2008 at 5:37 PM, Keith Goodman <[EMAIL PROTECTED]> wrote: > On Sun, Jun 1, 2008 at 2:19 PM, Alex Katebi <[EMAIL PROTECTED]> > wrote: > > Hi All, > > > > I have a in-memory database with some tables. Each table has a single > > record writer and multiple readers. > > Readers and writes prepare their own sqlite3_stmt for the db. Everyone > > operates within a single thread. > > What happens if a reader wants to read a record that has been deleted by > the > > writer? > > I must be too new to understand the question. But if the record is > deleted then you can't select it. Are you worried about a race > condition? I think sqlite takes care of those with locks. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] reading a row that has been deleted
On Sun, Jun 1, 2008 at 2:19 PM, Alex Katebi <[EMAIL PROTECTED]> wrote: > Hi All, > > I have a in-memory database with some tables. Each table has a single > record writer and multiple readers. > Readers and writes prepare their own sqlite3_stmt for the db. Everyone > operates within a single thread. > What happens if a reader wants to read a record that has been deleted by the > writer? I must be too new to understand the question. But if the record is deleted then you can't select it. Are you worried about a race condition? I think sqlite takes care of those with locks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] reading a row that has been deleted
Hi All, I have a in-memory database with some tables. Each table has a single record writer and multiple readers. Readers and writes prepare their own sqlite3_stmt for the db. Everyone operates within a single thread. What happens if a reader wants to read a record that has been deleted by the writer? Thanks! -Alex ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users