I created a test file. It is attached in this email. I can not see any
locking happening at all.
On Wed, Jul 2, 2008 at 4:25 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> Alex Katebi <[EMAIL PROTECTED]> wrote:
> > Do I need to enable shared cache mode plus read uncommitted option?
>
> You only have one connection (one call to sqlite3_open), right? Then it
> doesn't matter. "Shared" only makes a difference if there are at least
> two connections to share between.
>
> > Also you mentioned earlier:
> > "(but you will experience "dirty reads" with all the attendant
> > problems)."
> >
> > What is a dirty read? What problems does it cause?
>
> Dirty read is another term for read uncommitted. Your select statement
> may see changes made to the database while the statement is still
> active. This is especially "interesting" if you have a query that may
> scan the same table several times. For example:
>
> select * from table1
> where exists (select * from table2 where table2.value = table1.value);
>
> Suppose you have two records in table1 both having value=1 - let's call
> them A and B. Looking at the statement, one would think that, regardless
> of what's in table2, it should always return both A and B, or neither.
>
> So, you step through the statement above. For each record in table1, it
> scans table2 in search of a matching record. At some point, a call to
> sqlite3_step returns record A. Then you run another statement that
> deletes one and only record from table2 that had value=1. A subsequent
> sqlite3_step call won't find record B anymore. So you get A but not B,
> which may be surprising.
>
> Igor Tandetnik
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
#include <stdio.h>
#include <unistd.h>
#include <sqlite3.h>
int busy(void* arg, int cnt)
{
printf("%s: arg %p, cnt %d\n", __func__, arg, cnt);
return 1;
}
int main()
{
const char* zSql;
sqlite3* db;
sqlite3_stmt* stmt, *stmt2, *stmt3;
int rc;
if((rc = sqlite3_open(":memory:", &db)))
{
printf("open rc = %d\n", rc);
exit(1);
}
rc = sqlite3_busy_handler(db, busy, 0);
printf("busy_handler rc = %d\n", rc);
zSql = "create table t(a,b)";
rc = sqlite3_prepare_v2(db, zSql, -1, &stmt, 0);
printf("prepare rc = %d, %s\n", rc, zSql);
rc = sqlite3_step(stmt);
printf("step rc = %d\n", rc);
rc = sqlite3_finalize(stmt);
printf("finalize rc = %d\n", rc);
zSql = "insert into t values('alex','katebi')";
rc = sqlite3_prepare_v2(db, zSql, -1, &stmt, 0);
printf("prepare rc = %d, %s\n", rc, zSql);
rc = sqlite3_step(stmt);
printf("step rc = %d\n", rc);
rc = sqlite3_finalize(stmt);
printf("finalize rc = %d\n", rc);
zSql = "select * from t";
rc = sqlite3_prepare_v2(db, zSql, -1, &stmt2, 0);
printf("stmt2: prepare rc = %d, %s\n", rc, zSql);
rc = sqlite3_step(stmt2);
printf("stmt2: step rc = %d\n", rc);
zSql = "insert into t values('alex','katebi')";
rc = sqlite3_prepare_v2(db, zSql, -1, &stmt, 0);
printf("prepare rc = %d, %s\n", rc, zSql);
rc = sqlite3_step(stmt);
printf("step rc = %d\n", rc);
rc = sqlite3_finalize(stmt);
printf("finalize rc = %d\n", rc);
rc = sqlite3_prepare_v2(db, zSql, -1, &stmt3, 0);
printf("stmt3: prepare rc = %d, %s\n", rc, zSql);
rc = sqlite3_step(stmt3);
printf("stmt3: step rc = %d\n", rc);
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
