Am 14.02.2011 11:56, schrieb Henning Basold:
Am 27.01.2011 08:40, schrieb Henning Basold:
Hi,
I recently ran into a problem with prepared statements in SQLite3.
I use a prepared statement with long life time because it's very
complex and not executed concurrently. But after execution no
modifying statements could be executed in that database because it
was locked.
After some investigation I found
http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked . So
sqlite3_reset has to be called on the statement. As the library has
no knowledge about the point in time the statement is no longer
needed (multi-row sets etc.), I thought something like an explicit
reset is needed.
I attached a patch which introduces a reset method on statements. It
has a no-op implementation for all backends except SQLite. The patch
also contains a test case which does not run without the reset. It
leads to the error "SOCIERROR: sqlite3_statement_backend::loadOne:
database is locked".
All this is tested on Windows XP and Ubuntu 10.10 with SQLite 3.7.2.
Best Regards
Henning
Hi,
as there was no response I retry my request for the integration of the
patch. The main problem I had was explained in my last mail.
But I must admit that my statement "the library has no knowledge about
the point in time the statement is no longer needed" was made a bit
fast. Attached is a revised patch. It is an extension of the last. The
"reset" methods are preserved as the backend really does not know when
to reset the statement. But a call to it is added to the destructor of
the "statement" class. So if one uses a prepared statement she is
encouraged to make a copy of the statement. This is some kind of
"guard" which can do the reset automatically.
Also a bug was fixed (call on a deleted backend) and "reset" should
not throw any longer because it is called inside a destructor.
Finally I have updated the docs.
Hopefully this patch gets applied as I can not reuse the prepared
statement in my application.
Best regards
Henning
Ouch,
forgot the patch ....
Best regards
Henning
>From 8916b9b99cf663d5923631ae6c6f2a3e508a17c8 Mon Sep 17 00:00:00 2001
From: Henning Basold <[email protected]>
Date: Mon, 14 Feb 2011 11:43:57 +0100
Subject: [PATCH] Added reset facilities for statements
The need arises in using SQLite with prepared statements.
If one uses a prepared statement in SQLite then other connections
can not aquire a write lock after executing that statement
if sqlite3_reset isn't called. This is reflected by the test
case which causes a "db locked" error.
See http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked.
Also updated the documentation.
---
doc/reference.html | 4 +++
doc/statements.html | 29 ++++++++++++++++++++
src/backends/sqlite3/soci-sqlite3.h | 1 +
src/backends/sqlite3/statement.cpp | 9 ++++++
src/backends/sqlite3/test/test-sqlite3.cpp | 39 ++++++++++++++++++++++++++++
src/core/soci-backend.h | 2 +
src/core/statement.cpp | 8 +++++
src/core/statement.h | 9 +++++-
8 files changed, 100 insertions(+), 1 deletions(-)
diff --git a/doc/reference.html b/doc/reference.html
index bebe227..15c9f5e 100644
--- a/doc/reference.html
+++ b/doc/reference.html
@@ -338,6 +338,7 @@ public:
void exchange(<i>IT</i> const & i);
void exchange(<i>IT</i> const & u);
void clean_up();
+ void reset();
void prepare(std::string const & query);
void define_and_bind();
@@ -366,6 +367,7 @@ statement stmt(sql);
<li>Constructor accepting the result of using <code>prepare</code>
on the <code>session</code> object, see example provided above for the
<code>session</code> class.</li>
+ <li>Destructor which cleans up or resets referenced prepared statements.</li>
<li>Copy operations.</li>
<li><code>alloc</code> function, which allocates necessary internal resources.</li>
<li><code>bind</code> function, which is used to bind the <code>values</code>
@@ -376,6 +378,8 @@ they expect the result of calling the <code>into</code> or <code>use</code>
functions and are normally invoked automatically.</li>
<li><code>clean_up</code> function for cleaning up resources, normally
called automatically.</li>
+ <li><code>reset</code> function for resetting prepared statements, normally
+called automatically.</li>
<li><code>prepare</code> function for preparing the statement for
repeated execution.</li>
<li><code>define_and_bind</code> function for actually executing the
diff --git a/doc/statements.html b/doc/statements.html
index 5533216..84dd627 100644
--- a/doc/statements.html
+++ b/doc/statements.html
@@ -82,6 +82,35 @@ to fall back to this one-phase behaviour. Simply, pass
<code>-DSOCI_POSTGRESQL_NOPREPARE=ON</code> variable to CMake.</p>
</div>
+<div class="note">
+<p><span class="note">Portability note:</span></p>
+<p>A prepared statement may be saved across method calls e.g. as a
+class member. But if that is done one should make a copy of the
+statement before executing it. Copying a statement is cheap, so don't worry.</p>
+
+<p>
+Example:
+<pre class="example">
+class A {
+ // ...
+ session sql;
+ statement s;
+ A() : s(sql.prepare << ...){}
+
+ void someMethod(){
+ // use prepared statement making a copy
+ statement c(s);
+ s.execute ...
+ }
+};
+</pre></p>
+
+<p>For example the SQLite3 backends needs this. There a statement must be reseted
+if it is not destroyed. Otherwise the database may be locked for writing after execution.
+This reset is done in the destructor of statement as this is the only point where it is
+known that a statement is no longer in use.</p>
+</div>
+
<h3 id="rowset">Rowset and iterator-based access</h3>
<p>The <code>rowset</code> class provides an alternative means of executing queries and accessing results using STL-like iterator interface.</p>
diff --git a/src/backends/sqlite3/soci-sqlite3.h b/src/backends/sqlite3/soci-sqlite3.h
index 3f9d2fe..2ee2874 100644
--- a/src/backends/sqlite3/soci-sqlite3.h
+++ b/src/backends/sqlite3/soci-sqlite3.h
@@ -167,6 +167,7 @@ struct sqlite3_statement_backend : details::statement_backend
virtual void clean_up();
virtual void prepare(std::string const &query,
details::statement_type eType);
+ virtual void reset();
void reset_if_needed();
virtual exec_fetch_result execute(int number);
diff --git a/src/backends/sqlite3/statement.cpp b/src/backends/sqlite3/statement.cpp
index 0933c75..7d40fa3 100644
--- a/src/backends/sqlite3/statement.cpp
+++ b/src/backends/sqlite3/statement.cpp
@@ -68,6 +68,15 @@ void sqlite3_statement_backend::prepare(std::string const & query,
databaseReady_ = true;
}
+void sqlite3_statement_backend::reset()
+{
+ if (stmt_)
+ {
+ int res = sqlite3_reset(stmt_);
+ databaseReady_ = (SQLITE_OK == res);
+ }
+}
+
// sqlite3_reset needs to be called before a prepared statment can
// be executed a second time.
void sqlite3_statement_backend::reset_if_needed()
diff --git a/src/backends/sqlite3/test/test-sqlite3.cpp b/src/backends/sqlite3/test/test-sqlite3.cpp
index 93d26dd..7aeff4a 100644
--- a/src/backends/sqlite3/test/test-sqlite3.cpp
+++ b/src/backends/sqlite3/test/test-sqlite3.cpp
@@ -258,6 +258,44 @@ void test5()
std::cout << "test 5 passed" << std::endl;
}
+// Test reused prepared statement which would lock the database for writing without reset.
+// This happens only when two different connections to the database are used
+// where the first is "assigned" to the prepared statement and the second one
+// is used for writing.
+void test6()
+{
+ {
+ // A file for the database has to be used as in memory databases
+ // can not be shared throughout connections.
+ session sql1(backEnd, "test6.db");
+ session sql2(backEnd, "test6.db");
+
+ test4_table_creator tableCreator(sql1);
+
+ sql1 << "insert into soci_test(name) values('john')";
+ sql1 << "insert into soci_test(name) values('james')";
+
+ // prepared statement which is "reused"
+ int key1;
+ std::string name1;
+ statement stTpl = (sql1.prepare << "select * from soci_test", into(key1), into(name1));
+
+ {
+ // take a reference to the prepared statement
+ // when it goes out of scope, the prepared statement will be reseted
+ statement st(stTpl);
+ st.execute(true);
+ assert(name1 == "john");
+ }
+
+ {
+ std::string name2 = "james";
+ sql2 << "delete from soci_test where name = :name", use(name2);
+ }
+ }
+ std::cout << "test 6 passed" << std::endl;
+}
+
// DDL Creation objects for common tests
struct table_creator_one : public table_creator_base
{
@@ -359,6 +397,7 @@ int main(int argc, char** argv)
test3();
test4();
test5();
+ test6();
std::cout << "\nOK, all tests passed.\n\n";
diff --git a/src/core/soci-backend.h b/src/core/soci-backend.h
index 0576569..365fe67 100644
--- a/src/core/soci-backend.h
+++ b/src/core/soci-backend.h
@@ -147,6 +147,8 @@ public:
virtual void alloc() = 0;
virtual void clean_up() = 0;
+ // must not throw
+ virtual void reset(){}
virtual void prepare(std::string const& query, statement_type eType) = 0;
diff --git a/src/core/statement.cpp b/src/core/statement.cpp
index 582a6a9..2c9d37b 100644
--- a/src/core/statement.cpp
+++ b/src/core/statement.cpp
@@ -705,6 +705,14 @@ void statement_impl::dec_ref()
}
}
+void statement_impl::reset()
+{
+ if (backEnd_ != NULL)
+ {
+ backEnd_->reset();
+ }
+}
+
standard_into_type_backend *
statement_impl::make_into_type_backend()
{
diff --git a/src/core/statement.h b/src/core/statement.h
index e639a84..9b3c50e 100644
--- a/src/core/statement.h
+++ b/src/core/statement.h
@@ -55,6 +55,7 @@ public:
void describe();
void set_row(row * r);
void exchange_for_rowset(into_type_ptr const & i);
+ void reset();
// for diagnostics and advanced users
// (downcast it to expected back-end statement class)
@@ -135,7 +136,10 @@ public:
: impl_(new details::statement_impl(s)) {}
statement(details::prepare_temp_type const & prep)
: impl_(new details::statement_impl(prep)) {}
- ~statement() { impl_->dec_ref(); }
+ ~statement() {
+ impl_->reset();
+ impl_->dec_ref();
+ }
// copy is supported for this handle class
statement(statement const & other)
@@ -147,6 +151,7 @@ public:
void operator=(statement const & other)
{
other.impl_->inc_ref();
+ impl_->reset();
impl_->dec_ref();
impl_ = other.impl_;
}
@@ -191,6 +196,8 @@ public:
impl_->exchange_for_rowset(i);
}
+ void reset() { impl_->reset(); }
+
// for diagnostics and advanced users
// (downcast it to expected back-end statement class)
details::statement_backend * get_backend()
--
1.7.1
------------------------------------------------------------------------------
The ultimate all-in-one performance toolkit: Intel(R) Parallel Studio XE:
Pinpoint memory and threading errors before they happen.
Find and fix more than 250 security defects in the development cycle.
Locate bottlenecks in serial and parallel code that limit performance.
http://p.sf.net/sfu/intel-dev2devfeb
_______________________________________________
Soci-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/soci-users
