Re: [sqlite] Understanding database lock

[P Kishor]

There are many problems with your code. See below.

On Wed, Nov 11, 2009 at 12:50 AM, Akash Rao  wrote:
..
> I have a perl script that add numbers 1-1000 into a db.
>
> Here is the code:
> =
> use DBI;
>
> my $number;
>
> for ($number=0;$number <= 1000;$number++)
>  {
> my $dbh = DBI->connect("dbi:SQLite:dbname=/opt/test.db","","",
>                      {RaiseError => 1, AutoCommit => 0});
>
>   $dbh -> do("INSERT INTO test1 VALUES('$number')");
>

You don't want to connect to the database on every iteration of the
loop. Kinda defeats the purpose of a db connection.

>   $dbh -> commit();
>   $dbh->disconnect();
>  };
> 
>
..

Use the following, more perlish, code --


use DBI;

# The db connection has been moved out of the loop, and is now
# created only once and reused.
my $dbh = DBI->connect("dbi:SQLite:dbname=/opt/test.db","","",
 {RaiseError => 1, AutoCommit => 0});

# Prepare a statement with bind vars and reuse it in the loop
my $sth = $dbh->prepare("INSERT INTO test1 VALUES (?)");

for my $number (0 .. 1000) {
 $sth->execute($number);
};

# Commit and disconnect outside the loop
$dbh -> commit();
$dbh->disconnect();


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Understanding database lock

[Akash Rao]

Roger,

Thanks. I have read this a few times. But, i am trying to figure out how the
perl DBI for sqlite works with Sqlite's lock.

Any perl gurus here, please help.

Thanks,
Akash

On Wed, Nov 11, 2009 at 12:42 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Akash Rao wrote:
> > Wanted to understand the sqlite database lock a little better.
>
> Read this:
>
>  http://www.sqlite.org/lockingv3.html
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAkr6Y+oACgkQmOOfHg372QTxtQCbB/loEO/vENj2HHb9HXVN4xol
> 8EsAn2/OsmGiUp4ymdirGG+9ihDsBVVL
> =Je68
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Understanding database lock

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Akash Rao wrote:
> Wanted to understand the sqlite database lock a little better. 

Read this:

  http://www.sqlite.org/lockingv3.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkr6Y+oACgkQmOOfHg372QTxtQCbB/loEO/vENj2HHb9HXVN4xol
8EsAn2/OsmGiUp4ymdirGG+9ihDsBVVL
=Je68
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Understanding database lock

[Akash Rao]

Hello,

Wanted to understand the sqlite database lock a little better. Especially
while using perl DBI-sqlite module.

I did some tests and wanted to know if this is a known way of sqlite's
working.

I have a perl script that add numbers 1-1000 into a db.

Here is the code:
=
use DBI;

my $number;

for ($number=0;$number <= 1000;$number++)
 {
my $dbh = DBI->connect("dbi:SQLite:dbname=/opt/test.db","","",
  {RaiseError => 1, AutoCommit => 0});

   $dbh -> do("INSERT INTO test1 VALUES('$number')");

   $dbh -> commit();
   $dbh->disconnect();
 };


Tests:

Test 1. When two instances of the script are run at the same time (from two
different terminals), the update gets done. Though, there is a delay in the
second instance and finishes after the first one is done.

Test 2. Run one instance of the script and try to add to the db from the
sqlite3 command line (at the same time), I get "database locked" message.

Test 3. Run one instance of the script and simultaneously do a "select *
test1" from the command line, it does not show all the rows in the database.

I plan to use Apache+Perl+Sqlite to build a website that has about 100 users
updating the db through the browser.

I am using Sqlite 3.6.16 on RHEL.

Question 1: Do i need to worry about retry mechanism for "sqlite_busy or
database locked" scenarios. I do not intend to use the sqlite3 command line
for making updates.

Question 2: Is there a way to check if the database is busy or locked while
using Perl DBI?

Any pointers and sample code will help me immensely.

Thanks in advance.

Regards,
Akash
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users