[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    [sqlite] Updating tables in threaded app: a few question
From:       Karim Ryde <karim () bredband ! net>
Date:       2005-05-25 7:40:39
Message-ID: 200505250940.40157.karim () bredband ! net
[Download RAW message or body]

Hi!

I have a table with 19000 rows which needs to updated and accessed by 
different threads.

One approach to avoid locking the table as much is to create a TABLE TEMP 
mytable and as last action in threads to move rows from mytable_tmp to 
mytable, like this:

BEGIN TRANSACTION;
CREATE TEMP TABLE mytable_tmp  ...
INSERT INTO mytable_tmp SELECT * FROM mytable;
COMMIT TRANSACTION;

BEGIN TRANSACTION;
UPDATE mytable_tmp SET...
COMMIT TRANSACTION;

BEGIN TRANSACTION;
DELETE FROM mytable;
INSERT INTO mytable SELECT * FROM mytable_tmp;
DROP TABLE mytable_tmp;
COMMIT TRANSACTION;

Or is there a better way?
Can the last transaction be achieved quicker?
Are indices in mytable automatically updated?
The PRIMARY KEY in mytable keeps incrementing can it be resetted or should I 
DROP mytable?

Many thanks for any pointers...

Regards,
/Karim
[prev in list] [next in list] [prev in thread] [next in thread]