Igor Tandetnik wrote:
> On 2/3/2014 3:21 PM, Yuriy Kaminskiy wrote:
>> Igor Tandetnik wrote:
>>> On 2/3/2014 1:07 PM, Baruch Burstein wrote:
>>>> 1) How does a transaction affect SELECTs? If I start a transaction
>>>> and do
>>>> an UPDATE/DELETE/INSERT, what data will a SELECT in the same
>>>> transaction
>>>> see?
>>>
>>> The new data. A transaction always sees its own changes.
>>>
>>>> What about a SELECT in a different connection?
>>>
>>> Depends. In journal mode, the reader transaction is blocked while a
>>> writer is in progress; you won't be able to run that SELECT statement
>>
>> Erm, wrong.
>>
>> [1] sqlite> create table t (i);
>> [1] sqlite> insert into t values (1);
>> [1] sqlite> begin;
>> [1] sqlite> insert into t values (2);
>> [2] sqlite> begin;
>> [2] sqlite> /* as you see, there are no problem in starting transaction
>> concurrently with pending update in [1]: */
>> [2] sqlite> select * from t;
>> 1
>> [2] sqlite> /* as you see, SELECT is also successful and returns
>> unmodified data: */
>
> Ah, right. I oversimplified. [1] holds a reserved lock, indicating
> intention to write eventually; changes are parked in an in-memory cache
> for now. A reserved lock allows new readers to acquire shared locks.
>
> At some future point, the writer wants to commit, or else the amount of
> changes becomes large enough that they cannot be held in RAM and need to
> be spilled to disk. At this point, the writer would escalate to PENDING
> lock, wait until all current readers clear while not allowing any new
> ones, then escalate once more to EXCLUSIVE lock, and hold it until the
> transaction is committed or rolled back.
Thanks for correction.
Hmm... that suggests there can be other failure mode (with rollback journal):
(long-enough) concurrent read-only transaction can break (sufficiently-big)
insert transaction, even if read-only transaction will be ended before COMMIT in
update transaction. It would be nasty (and render "BEGIN IMMEDIATE workaround"
problematic)...
...however, I was not able to reproduce it in test.
How sqlite is supposed to behave when
*) there are read-only transaction;
*) there are update transaction on other connection;
*) cache space is exhausted by update transaction;
*) sqlite was not able to upgrade RESERVED lock to EXCLUSIVE due to presence of
SHARED lock, even after waiting for busy_timeout;
?
It can either fail update statement (but I don't see that in test below: it
sleeps for busy_timeout, but *does not* return error), or keep on storing data
in memory [it *does not* write anything] (thus using over specified cache size?
[and eventually overflow virtual memory?]), or what?
>> There are *different* restriction in rollback journal mode: you cannot
>> *COMMIT*
>> in [1] while transaction in [2] is still active.
>
> Well, there are *both* sets of restrictions - the kind that I described,
> and the kind that you describe. Which set of restrictions applies
> depends on where in its lifecycle the writer is. If it's still holding a
> RESERVED lock, then new readers are allowed, and the writer is blocked
> until they clear. If it's holding a PENDING or EXCLUSIVE lock, then
> readers are blocked until the writer clears.
--
=== test.pl ===
#!/usr/bin/perl -l
use blib;
use IO::Handle;
use DBI;
DBI -> trace( 1 );
my $dbh = DBI->connect( "dbi:SQLite:dbname=/tmp/ttt.db3","","", {
RaiseError=>1,
});
my $dbh2 = DBI->connect ( "dbi:SQLite:dbname=/tmp/ttt.db3","","", {
RaiseError=>1,
});
$dbh->do("pragma page_size = 1024;");
$dbh->do( "pragma cache_size = 4; ");
$dbh2->do( "pragma cache_size = 4; ");
$dbh->do("CREATE TABLE IF NOT EXISTS t (i)");
my $sth = $dbh->prepare( "SELECT * FROM t" );
my $sth2 = $dbh2->prepare( "SELECT * FROM t" );
my $ins_sth = $dbh->prepare("INSERT INTO t VALUES (?)");
$dbh->func( 10_000, 'busy_timeout' );
$dbh2-> do( "BEGIN;");
# acquire SHARED lock in dbh2:
$sth2->execute();
$sth2->fetch();
$dbh-> do( "begin;" );
my $j = 0;
for my $i (1..1_000) { $j += $ins_sth->execute($i); }
print STDERR "j = $j\n";
$sth2->finish();
my $i = 0;
$sth->execute();
while($sth->fetch) { $i++; }
$sth->finish;
print STDERR "i = $i\n";
# release SHARED lock in dbh2; fails if next two lines swapped
$dbh2->do("COMMIT");
$dbh->do("commit");
$dbh->disconnect;
$dbh2->disconnect;
___END___
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
