I've been doing some further reading and I think that I already have the
answer and it's "No, no way to avoid this".
As far as I've read, when you start any update function, SQLite makes an
exclusive write lock to the table. As far as I'm excuting from inside an
update trigger, the database is "exclusivily write locked", so if that
trigger tries to write to the DB, we will end up in a deadlock.
The only thing that can "save" me from this behavior is if there is someway
to tell SQLite that the update is over, before the trigger finished.
I've tried to put a commit inside the trigger, but I get a "syntax error".
I'm working in to aporaches to the workarund. One i ssplit the table in two
(due to the logic of my aplication, maybe I can do this, cause the files
updated by the trigger can have some kind of logic that I can use to split
the table in 2 different tables). The other is to start a background php
task that wait till the DB lock is released and then, tries the insert.
Any suggestion?
thx again
2009/8/31 Alejandro Ruiz-Oriol <aruiz...@itelsys.com>
> Ok, you we're right!!
>
> The trigger fires a PHP function that connects back to de database and, in
> that connection I didn't register the php functions. Just in case someone
> runs in the same problem, this is a strange situation, because the function
> exists for PHP, but no for SQLite. So, the first time you have check if the
> php function exist with a code like this
>
> if (!function_exists(test))
> {
>
> include_once("/var/www/arduino/sqlite_functions.php");
> }
>
> and, after that, always register to SQLite.
> $dbh->sqliteCreateFunction('Test','Test');
>
>
> But, now, I'run into another problem (don't know if it's better to open
> another thread)
>
> As I said, the trigger fires a PHP script that connects back to the
> database, and tries to update another row in the same table, and, when it
> executes the update statement, it gets frozen. In some cases, I get the
> "Database lock" error,
>
> I don't really think this is a deadlock, as far as I'm not writing to the
> same row and the trigger is configured as "after update", so the row shoul
> be released by the time trigger is fired, but I rather think that SQLite
> considers this new connection as completely new and it's blocking the whole
> database.
>
> Do you think that this can be avoided somehow?
>
> Thx in advance
> Chano
>
> 2009/8/29 Kees Nuyt <k.n...@zonnet.nl>
>
> On Fri, 28 Aug 2009 19:50:56 +0200, Alejandro Ruiz-Oriol
>> <aruiz...@itelsys.com> wrote:
>>
>> >Thank's Swithun
>> >
>> >but I still have trouble.
>> >
>> >Ok, I find out how to register functions with
>> >PDO_Sqlite extensions. Just in case someone
>> >is in the same situation, the way to do it is this:
>> >
>> >$dbh = new PDO('sqlite:/whatever.sqlite');
>> >$dbh->sqliteCreateFunction('Test','Test');
>> >
>> >But I still have a problem:
>> >
>> >If I use direct the test funcion in a quuery like
>> >
>> >$res=$dbh->query("select test() from table");
>> >
>> >it works, but if function test is invoked from a triiger it will say, my
>> >sentence will be somethin like
>> >
>> >$modulo=$dbh->query("UPDATE test SET x = 1");
>> >
>> >I get this:
>> >
>> > [0] => HY000
>> > [1] => 1
>> > [2] => no such function: Test
>> >
>> >seems like the trigger is not using the same "$dbh"
>> >
>> >¿any clue?
>>
>> Are you sure the function is registered on the $dbh with
>> $dbh->sqliteCreateFunction(...);
>> on the $dbh in which the trigger fires?
>>
>> You have to do that in every script that instantiates the
>> object.
>>
>> >Thx
>> --
>> ( Kees Nuyt
>> )
>> c[_]
>> _______________________________________________
>> 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
