Hi All,
While using SQLite dll Version 3.3.4 on Windows
- Multiple threads/processes access SQLite database,
- Each thread does some SELECTs, INSERTs or UPDATEs.
Scenario 1
If action of some user needs to execute multiple SELECT statements
(read-only, no plan to write), it needs to start explicit transaction to get
consistent reads across read-only multiple statements.
Q1. Which one is preferable -BEGIN TRANSACTIN or BEGIN IMMEDIATE ?
Q2. What is preferable After Multiple SELECT statements gets over -CMMIT
or RLLBACK ? (note: no change is made since only SELECTs)
Scenario 2
If action of some user needs to execute only single SELECT statement
(read-only, no plan to write)
As what I understand, there is no point in wrapping such single SELECT in
transaction.
Q3. What is preferable, whether to start explicit transaction or not for
single SELECT ?
I truly appreciate any help/guidance.
Rohit

Thanks a lot for the answers.

Still I have a doubt (and a question).

Quote from SQLite Document ()

"After a BEGIN IMMEDIATE, you are guaranteed that no other thread or process
will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN
EXCLUSIVE."

As I understand, it means after issuing BEGIN IMMIDIATE for read-only
SELECT(s), no other process can do BEGIN IMMIDIATE even if its for read-only
purpose. Even new reader will be blocked if it tries to do BEGIN IMMEDIATE.

So if I use BEGIN IMMEDIATE for multiple SELECTs, no other reader
thread/process can do BEGIN IMMEDIATE even if it has read-only SELECTs

If this is correct, for multiple SELECT statements (read-only, no plan to
write),

Which one is preferable -BEGIN TRANSACTIN or BEGIN IMMEDIATE ?

Rohit

Thanks for the answer and clarification.

BEGIN IMMEDIATE blocks writers, not readers.

I think, BEGIN IMMEDIATE surely blocks writers.
And also blocks new reader(s) if any new reader tries to do BEGIN IMMEDIATE.

Is this correct ?

Ref: Quote from SQLite Document
()
"After a BEGIN IMMEDIATE, you are guaranteed that no other thread or process
will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN
EXCLUSIVE."

Rohit

Thanks for clearing doubt.

Now question is

While using SQLite dll Version 3.3.4 on Windows
- Multiple threads/processes access SQLite database,
- Each thread does some SELECTs, INSERTs or UPDATEs.

Wrapping all read-only SELECEs with BEGIN TRANSACTIN
and using BEGIN EXCLUSIVE to wrap all UPDATEs or INSERTs (including their
related SELECTs),

Are their possibilities of writer starvation ?
And if yes, what is the preferable solution ? (I mean what is the better
approach to handle that)

Thanks again.
Rohit

Many thanks for helping.

While using SQLite dll Version 3.3.4 on Windows
- Multiple threads/processes access SQLite database,
- Each thread does some SELECTs, INSERTs or UPDATEs.

If for some single SELECT (where user input is used in SQL statement, so to
avoid SQL injection),
sqlite3_prepare()/sqlite3_bind()/sqlite3_step()/sqlite3_finalize() are
used
then in that case
Q1. Which one is preferable -BEGIN TRANSACTIN or BEGIN IMMEDIATE ?

Q2. In the sample code below, if I remove two statements containing "BEGIN
TRANSACTIN;" and "CMMIT TRANSACTIN;" then for how long lock will be
acquired i.e. at which point of time lock will be acquired and which point
of time lock will be released ?

Please throw some light on that.

Thanks
Rohit

/* Sample code */
sqlite3* pDB;
sqlite3_stmt* pStmt;
char* szError = 0;
char* szTail = 0;
if( sqlite3_open("test.db", &pDB) != SQLITEK )
{
printf("Couldn't open the database.\n");
exit(1);
}

string name;
/* get from user input */
name = "ABCD"; // for testing

nRet = sqlite3_exec(pDB, "BEGIN TRANSACTIN;", 0, 0, &szError);
<<<<<<<<<<

const char* szSQL = "SELECT id, name, birthdate FRM table1 WHERE name = ?;"
;
nRet = sqlite3_bind_text(mpVM, nParam, szValue, -1, SQLITE_TRANSIENT);

if( sqlite3_prepare(pDB, szSQL, -1, &pStmt, &szTail) != SQLITEK )
throw "sqlite3_prepare Failed";

if ( sqlite3_step(pStmt) == SQLITE_DNE )
{
int i=0;
int empid = sqlite3_column_int(pStmt, ++i);
string name = (const char*) sqlite3_column_text(pStmt, ++i);
double birthdate = sqlite3_column_double(pStmt, ++i);

/* print something */
std::cout << id << "-" << name << "-" << birthdate << endl;
}

nRet = sqlite3_finalize(pStmt);

nRet = sqlite3_exec(pDB, "CMMIT TRANSACTIN;", 0, 0, &szError);
<<<<<<<<<<