Updating tables in threaded app: a few question

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Updating tables in threaded app: a few question

Bugzilla from karim@bredband.net
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