Re: FET_BUF_SIZE and Data Consistency

In article <01bd0897$1dc0cb20$65020a0a@dsamsson.ilndc.com>, David Samson <dsamson@ndsisrael.com> writes > I've seen alot of postings about changing FET_BUF_SIZE to improve >performance. We've got another concern: Our application has rows which are >150 bytes each. Therefore, with a default fetch buffer of 4096, we're >pulling in about 27 rows at a time. I don't have a problem with >performance. I DO have a problem with DB consistency. > > We've got 2 processes working against the DB. One is reading into the >fetch buffer("reader"). The other process is inserting and deleting rows >(let's call it the "writer"). If writer deletes a row which is already in >reader's fetch buffer (but not yet pulled with a "fetch" command), reader >will process a row which doesn't exist anymore. This seems to go against >the concurrency handling protocols. Specifically, if I'm using Committed >Read, when I fetch a row it should only return to me rows which exist at >the moment of the fetch. In fact, it's returning rows which existed WHEN >THE BUFFER WAS FILLED! > > To deal with this, we tried to reduce the FET_BUF_SIZE. However, I >can't set it below 4096. (Also, the reader needs a very high throughput >rate so I wasn't happy about shrinking the buffer size.) I could probably >also put a lock on every row in the fetch buffer but that will also kill >throughput. > > Has anybody else come across this? No, but I can imagine it happening. > Any clever solutions? > I assume you have a unique key on each row that you can use to fetch it? Declare a second cursor for update. DECLARE CURSOR.... ...WHERE unique_key = ? ...FOR UPDATE Then when you need to lock one row do OPEN CURSOR ...USING <unique key value> FETCH CURSOR.. This will lock just one row. > Thanks in advance. > > David Samson > -- David Williams