Hi Richard,
On Sun, Nov 23, 2003 at 12:22:25PM -0500, D. Richard Hipp wrote:
> Lots of people seem to think that better concurrency in
> SQLite would be useful. But I am having trouble understanding
> why.
First, kind thanks for SQLite - it's a nice piece of work, and fits a project
I'm working on perfectly. I'm using SQLite as a ETL staging and transformation
database between a production transaction database environment and a
reporting database that supports long running, read-only queries.
Based on your list, I'm not really experiencing any of these situations,
with the possible exception of '... holding transactions open for an
extended period of time'. My situation is selecting from one table to
insert/update another table, as in (Tcl):
sqlite db1 my.db
sqlite db2 my.db
db1 eval "select * from foo" row {
# manipulate row values, lookup other values, etc.
db2 eval "insert into bar values( .... )"
}
I have to first create a temp table then 'insert into tmp_foo (...)
select * from foo" in order to avoid the whole db being locked during my
processing. This is a single process attaching to the database using two
handles.
So at a minimum, table level locking would benefit me. Your proposal 3.2
seems to best fit my model. Having 'read-only' selects would also be
satisfactory as in 4.1. I would suggest syntax similar to other databases,
such as:
select .... FOR UPDATE [ OF table ] PostgreSQL, Oracle
select .... from table WITH (NOLOCK) Sybase, MsSqlServer, per table
select .... WITH UR DB2 (uncommited read)
or perhaps with SET
SET TRANSACTION READ ONLY PostgreSQL
SET TRANSACTION READ UNCOMMITTED Sybase
I find my current use of temp tables to be a workable solution, although table
level locking would simplify my usage somewhat.
Best,
Tom
--
Tom Poindexter
[EMAIL PROTECTED]
http://www.nyx.net/~tpoindex/
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
