Re: [sqlite] Multiple SELECTs (and single SELECT) and TRANSACTION ?
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 TRANSACTION or BEGIN IMMEDIATE ?
Q2. In the sample code below, if I remove two statements containing "BEGIN
TRANSACTION;" and "COMMIT TRANSACTION;" 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", ) != SQLITE_OK )
{
printf("Couldn't open the database.\n");
exit(1);
}
string name;
/* get from user input */
name = "ABCD"; // for testing
nRet = sqlite3_exec(pDB, "BEGIN TRANSACTION;", 0, 0, );
<<---
const char* szSQL = "SELECT id, name, birthdate FROM table1 WHERE name = ?;"
;
nRet = sqlite3_bind_text(mpVM, nParam, szValue, -1, SQLITE_TRANSIENT);
if( sqlite3_prepare(pDB, szSQL, -1, , ) != SQLITE_OK )
throw "sqlite3_prepare Failed";
if ( sqlite3_step(pStmt) == SQLITE_DONE )
{
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, "COMMIT TRANSACTION;", 0, 0, );
<<---
--
View this message in context:
http://www.nabble.com/Multiple-SELECTs-%28and-single-SELECT%29-and-TRANSACTION---tf2072083.html#a5765261
Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Multiple SELECTs (and single SELECT) and TRANSACTION ?
It depends upon your application. For it to function optimally you should make each transaction on your application an SQL transaction, commit it on success or rollback if there is a problem. In that way you make each transaction atomic and maintain the integrity of your database. Since Sqlite locks the entire database when it is being modified, transactions which modify the database can be made exclusive. RohitPatel wrote: 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 TRANSACTION 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
Re: [sqlite] Multiple SELECTs (and single SELECT) and TRANSACTION ?
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 TRANSACTION 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 -- View this message in context: http://www.nabble.com/Multiple-SELECTs-%28and-single-SELECT%29-and-TRANSACTION---tf2072083.html#a5737733 Sent from the SQLite forum at Nabble.com.
[sqlite] Multiple SELECTs (and single SELECT) and TRANSACTION ?
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 TRANSACTION or BEGIN IMMEDIATE ? Q2. What is preferable After Multiple SELECT statements gets over -> COMMIT or ROLLBACK ? (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 -- View this message in context: http://www.nabble.com/Multiple-SELECTs-%28and-single-SELECT%29-and-TRANSACTION---tf2072083.html#a5704541 Sent from the SQLite forum at Nabble.com.
