Thread: Re: LOCK TABLE
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? Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com
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
Attachment
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.
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