Re: LOCK TABLE

On Sun, Sep 17, 2000 at 09:42:44AM -0700, K Parker wrote:

Sure you can lock a single row:

BEGIN;
SELECT .... FOR UPDATE ... ;
-- app messes with filesystem...
-- may or may not actually update the row
COMMIT;

Won't this do what you need?

Thanks - that's very useful to know.

However, it's not exactly what I'm looking for. I'll explain fully.

This table contains a list of messages. One of the fields is the
path to where the message is stored in plaintext on the filesystem
(it's not in the DB because it could be > 8K, I also don't want to
take up a lot of space). Another field is the boolean field 'archived'
- in other words, has this message been compressed - y/n?

So the app is doing regular maintainence. It goes through the messages,
and selects a list of the really old ones that are going to be compressed
to save space. Now, the app goes through the list, and does the actual
compression on disk. *Here* is where everything could get screwed
up - if someone queries the DB looking for a message that has been
compressed on the FS but the transaction hasn't yet been committed,
they'll read the compressed file off the HD and try to display it -
but it will be garbage. Of course, once the transaction is committed
and the 'archived' flag is toggled, the client app will know to
decompress the file before trying to read it. However, I'm worried
about the time in between the initial 'SELECT ... FOR UPDATE' and
when the transaction is committed.

One thing - the filename is the same whether the file is compressed or
not. So the client app can only look @ the 'archived' boolean to judge
if it needs to decompress the file.

If I wrap this inside a transaction, other queries will see the state of
the DB *before* the transaction was started. Which is bad - they will
see 'archived' is false, and read the compressed file from the FS as
though it was plaintext.

What I would like to happen is that if any queries want to access the row
while the transaction isn't committed, they should just poll until the
transaction is committed or rolled back. This way, they won't get *any*
results until the data is in a consistent state. Is this possible? Or am I
going about things the wrong way?

Thanks in advance,

Neil

--
Neil Conway <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed

Don't worry about people stealing your ideas. If your ideas are any
good, you'll have to ram them down people's throats.
-- Howard Aiken

On Sun, 17 Sep 2000, Neil Conway wrote:

This table contains a list of messages. One of the fields is the
path to where the message is stored in plaintext on the filesystem
(it's not in the DB because it could be > 8K, I also don't want to
take up a lot of space). Another field is the boolean field 'archived'
- in other words, has this message been compressed - y/n?

So the app is doing regular maintainence. It goes through the messages,
and selects a list of the really old ones that are going to be compressed
to save space. Now, the app goes through the list, and does the actual
compression on disk. *Here* is where everything could get screwed
up - if someone queries the DB looking for a message that has been
compressed on the FS but the transaction hasn't yet been committed,
they'll read the compressed file off the HD and try to display it -
but it will be garbage. Of course, once the transaction is committed
and the 'archived' flag is toggled, the client app will know to
decompress the file before trying to read it. However, I'm worried
about the time in between the initial 'SELECT ... FOR UPDATE' and
when the transaction is committed.

One thing - the filename is the same whether the file is compressed or
not. So the client app can only look @ the 'archived' boolean to judge
if it needs to decompress the file.

If I wrap this inside a transaction, other queries will see the state of
the DB *before* the transaction was started. Which is bad - they will
see 'archived' is false, and read the compressed file from the FS as
though it was plaintext.

If you always SELECT ... FOR UPDATE (in all transactions that access it),
then the second one will not see the DB state before the transaction is
started, because the row is locked and the second transaction won't be
able to get its lock and will instead wait. Admittedly this lowers your
ability to have concurrent reads of the same rows as well, so you would
want the other transactions to hold the lock for as short a time as
possible.

... selects a list of the really old [files] that are going
to be compressed to save space. ... the filename is the same
whether the file is compressed or not. So the client app can
only look @ the 'archived' boolean to judge if it needs to
decompress the file.

There's your mistake. If you can change this one behavior, your
problem goes away. Hopefully, you have a single function somewhere
that your clients all use to access the file. In that case, you
can:

* Always store the "uncompressed" name in the table.
* A client first looks under the "uncompressed" name,
then under the "compressed" name (which has an
extra/different extension, etc.)

When you compress, you compress from the old name to the new name.
As long as the old version is still there, clients will use it,
so there's no chance of someone trying to read the
incompletely-compressed
file. The nice thing about this strategy is that it's fairly
straightforward to change from your old system to this, at the
cost of a temporary increase in disk space.

If using two filenames is not possible for some reason, you might
try adding logic to the client to test the initial bytes of the
file to see if it's compressed or not. Assuming you're on Unix,
you can compress to a new file, then rename the compressed
file to the old filename, and it should work correctly.

If this message table is really as heavily used as you say,
a strategy that lets you avoid locking entirely seems worth
pursuing.

- Tim

How about adding a third state to the compressed flag --
"compressing"? Requires an extra update tho...

--
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.com
GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

On Sun, Sep 17, 2000 at 12:50:26PM -0700, Stephan Szabo wrote:

If you always SELECT ... FOR UPDATE (in all transactions that access it),
then the second one will not see the DB state before the transaction is
started, because the row is locked and the second transaction won't be
able to get its lock and will instead wait. Admittedly this lowers your
ability to have concurrent reads of the same rows as well, so you would
want the other transactions to hold the lock for as short a time as
possible.

I was wondering, if I do something like

select * from person order by surname for update limit 1 offset 10;

as there is no where clause, am I locking the whole table?

Cheers,

Patrick