On Mon, Oct 11, 2010 at 06:33:11AM -0500, Black, Michael (IS) scratched on the
wall:
> So rather than
> DELETE FROM TABLE WHERE datetime < oldtime;
>
> Do this;
> SELECT datetime from TABLE where datetime < oldtime;
> BEGIN
> for each record
> DELETE FROM TABLE where datetime=thistime;
> COMMIT
If you do something like this, you should always put the SELECT
*inside* the transaction. The transaction acts as a lock of sorts,
keeping the state of the database consistant. If you put the SELECT
outside the transaction, the database data can change between the
SELECT and the BEGIN. That means you may try to DELETE records that
are not there, or that no longer match the conditions of the SELECT.
In this case that is unlikely to be a big deal, but it is better to
keep the data consistant. It also streamlines the error handling.
Database programming is really a lot like multi-thread programming,
as there can be multiple clients making changes simultaniously. In
that sense, the whole database is a shared data structure. Just as
you would use mutex locks to protect manipulation of shared data
structures in multi-threaded programming, it is best to use
transactions to protect "whole operations" in database programming.
In this case, that includes the initial SELECT.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
