O grupo no qual você está postando é um grupo da Usenet. As mensagens postadas neste grupo farão com que o seu e-mail fique visível para qualquer pessoa na internet.
> begin work; > set isolation to dirty read; > select * from tab1; > insert into tab1 values ,value list.; --This automatically turns to > comitted read > select * from tab1; -- Back to dirty read; > commit work;
> Of course if you feel the need to use committed read all the time, you > can just use
> set lock mode to wait <wait time>
> and then you second app will wait, up till the time specified, for you > batch process to release the lock.
There is a problem that the batch processing takes tens minutes... The second problem is that I access DB from Java via JDBC (Hibernate) and I don't like (can not) have special code for Informix. I know that I can put isolation level and wait_time into my URL :-)
Ok, after talking with my DB2 expert, I understand what your problem is.
DB2, and other DB's let you set your lock timeout value at the instance/database level.
Informix gives you the same functionality, only at the client level.
Informix's defualt lock mode is NOT WAIT.
What that means in the scenario you gave is that the the second application would error out as soon as it hit a row the batch process uses.
The solution is to take advantage of Informix' SET LOCK MODE syntax.
SET LOCK MODE TO WAIT 30;
for example tells your applicatoin to try and obtain a lock for 30 second before reporting a locking issue. In normal situations 30 seconds is more than enough time, but on batch intensive boxes, I have seen some apps set their lock mode as high as 120.
----- Original Message ---- From: Lukas Barton <lu...@cnawr.cz> To: informix-l...@iiug.org Sent: Monday, September 25, 2006 10:11:17 AM Subject: Re: Let me be the first
Obnoxio The Clown wrote: Lukas Barton said: This makes Informix quite unuseable for OLTP in comibination with batch processing (the same time). Shit, you mean I've been doing it wrong for 20 years?
And how did you solve the problem I'am facing now: - I have two tables - I use read_commited transactions - User one runs batch process upon them that locks some rows in them for reading (because of locking next and previous record in indexes it also lock some other records) - User two wants to read his records which are locked by user one and must wait until batch process started by user one finishes
This works smoothly on Oracle, DB2 (PC and OS400), PostgreSQL, MS SQL, Firebird, Interbase,... where is not so stupid locking implemented....
You don't use transactions? (eg. dirty_reads only).
> Ok, after talking with my DB2 expert, I understand what your problem is.
> DB2, and other DB's let you set your lock timeout value at the > instance/database level.
> Informix gives you the same functionality, only at the client level.
> Informix's defualt lock mode is NOT WAIT.
> What that means in the scenario you gave is that the the second > application would error out as soon as it hit a row the batch process > uses.
> The solution is to take advantage of Informix' SET LOCK MODE syntax.
> SET LOCK MODE TO WAIT 30;
> for example tells your applicatoin to try and obtain a lock for 30 > second before reporting a locking issue. In normal situations 30 > seconds is more than enough time, but on batch intensive boxes, I > have seen some apps set their lock mode as high as 120.
I have not problem with waiting. The batch process diferent rows from that the second user wants to read! I mean I understand locking in Informix quite well. And I see it quite impractical in combination with transactions. Only row versioning may solve my problem.
Turn the first page of InformationWeek and you see a 2 page add for Sybase.
Double Echo <doubleecho@your. com> To Sent by: informix-l...@iiug.org informix-list-bou cc n...@iiug.org Subject Re: Let me be the first 09/25/2006 11:23 AM
OK, so why was CRN chosen as the mystical, magical trade rag to post this to?
As if they really get any attention beyond VARs reading them, if at all.
There needs to be a major magazine, preferably one like eWeek, or ComputerWorld that this appears in. I'm hoping against hope that if it indeed does show up in one of these that the writing will be better, and the new features more clearly articulated, because CRN gives anyone the idea that Informix just now got row-level locking. In most circles record-level and row-level are used interchangeably.
Even Sybase gets more news these days. Head over to ComputerWorld or eWeek and you'll see. And Sybase was the one that didn't have row-level locking until around 2000 or so. Now Informix has to fight yet another feature perception that wasn't there!
Madison Pruet wrote: > Double Echo wrote: >> You missed my point.
>> This is apparently one of the first articles ( in years btw ) to even >> talk about Informix and new technology. It was poorly written, and >> so far a casual sweep to other news sites turns up nothing. If an >> article has come out on CRN, where else will we expect to see more >> news? These kinds of articles are important, they get saved, printed, >> and referenced to, in building street credibility about the product.
>> And, as usual, a lot of people will not be in Anaheim, especially new >> customers outside the 20,000 customers mentioned in the article and >> the folks from Walmart who have apparently been responsible for keeping >> the product alive. So any articles that are written are incredibly >> important in their scope and content.
> And I would expect more to come out over the next few weeks, especially > after the conference.
> M.P.
>> Madison Pruet wrote: >>> Double Echo wrote:
>>>> Curious that this poorly written article provides an "announcement", >>>> so where is >>>> the IBM presence for this on the IBM website or other trade rags?
>>> What Arvind discussed was a prelude to the IOD conference in Aniheim >>> coming up in a few weeks. Other things will be described in the >>> conference, and in several of the regional IIUG mini-conferences >>> coming up. I wouldn't expect to see a whole lot of mention about the >>> other new functionality until after then.
>>> M.P.
>>>> Madison Pruet wrote: >>>>> Obnoxio The Clown wrote: >>>>>> Mark Townsend said:
>>>>>> "The next release, code-named Cheetah, is due to beta this spring >>>>>> and will >>>>>> feature *record-level locking*, technology carried over from the >>>>>> latest >>>>>> release of IBM DB2, said Arvind Krishna, vice president of database >>>>>> servers for IBM Software's Information Management Group, Somers, >>>>>> N.Y."
>>>>> There is a bit of mis-communication with this. What we are doing >>>>> is implementing a form of row versioning which will enable a select >>>>> to view the currently committed row rather than blocking on the >>>>> row when it is in the process of being updated and is locked.
>>>>> There's quite a few other really cool things that are being put >>>>> into Cheetah. Can't talk about them just yet, but will be doing so >>>>> at the conference in Aniheim.
>> And how did you solve the problem I'am facing now:
> Probably by using an index.
I am not so stupid not to use indexes. The problem is that the batch searches within all clients and account in bank acorrding - person number, company number, person name and birth date etc... And this data are extremly large. So that the processing last minutes.
It does not, because Informix locks also previous and next row in all indexes. So that the probability of locking rows that other user want to read is more than zero.
>>> And how did you solve the problem I'am facing now:
>> Probably by using an index.
> I am not so stupid not to use indexes.
But are you smart enough to use the RIGHT index?
> The problem is that the batch searches within all clients and account in > bank acorrding - person number, company number, person name and birth > date etc... > And this data are extremly large. So that the processing last minutes.
Let's think about this for a second. Person A is doing a batch routine that has to process every customer account in a branch (say branch X). Person B is doing interactive queries for random customers, some of which may be in branch X.
Let's say that Person B is looking into the details for Customer 123, who happens to bank at branch X.
If person A is currently processing customer 123, then it's only right that person B can't see the rows for customer 123. But if person A is processing some other customer's data, then I can't really see why the data for customer 123 should be locked.
Why would the data for customer 123 be locked?
-- Bye now, Obnoxio
"... no bill is required as no value was provided." -- Christine Normile
-- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
> It does not, because Informix locks also previous and next row in all > indexes. So that the probability of locking rows that other user want to > read is more than zero.
I'm fascinated by this assertion. I guess I've just been unbelievably lucky all my working life.
-- Bye now, Obnoxio
"... no bill is required as no value was provided." -- Christine Normile
-- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
> Let's think about this for a second. Person A is doing a batch routine > that has to process every customer account in a branch (say branch X). > Person B is doing interactive queries for random customers, some of which > may be in branch X.
No, peresn A runs a process for clients of branch X and person B reads clients of branch Y! Users own their data and run batch processing on them (in background threads).
> Let's say that Person B is looking into the details for Customer 123, who > happens to bank at branch X.
> If person A is currently processing customer 123, then it's only right > that person B can't see the rows for customer 123. But if person A is > processing some other customer's data, then I can't really see why the > data for customer 123 should be locked.
> Why would the data for customer 123 be locked?
All the processing is done in one transaction becase of consistency with data in the datamart (tables with clients, accounts, etc.).
> It does not, because Informix locks also previous and next row in all > indexes. So that the probability of locking rows that other user want to > read is more than zero.
> Lukas
sounds to me like you are using page level locking rather than row level locking
thats the only way I see informix locking rows other than the one you want to read
Interesting in that it doesn't note anything about supporting PeopleSoft, JD Edwards, Siebel, or other applications. -- Daniel Morgan University of Washington
Lukas Barton wrote: > > Let's think about this for a second. Person A is doing a batch routine > > that has to process every customer account in a branch (say branch X). > > Person B is doing interactive queries for random customers, some of which > > may be in branch X.
> No, peresn A runs a process for clients of branch X and person B reads > clients of branch Y! > Users own their data and run batch processing on them (in background > threads). > > Let's say that Person B is looking into the details for Customer 123, who > > happens to bank at branch X.
> > If person A is currently processing customer 123, then it's only right > > that person B can't see the rows for customer 123. But if person A is > > processing some other customer's data, then I can't really see why the > > data for customer 123 should be locked.
> > Why would the data for customer 123 be locked?
> All the processing is done in one transaction becase of consistency with > data in the datamart (tables with clients, accounts, etc.).
> Lukas
Ok the rows are locked becasue you choose them to be locked
row versioning would let you read old data that has been changed
dirty read would let you read new data that might not be committed
would row versioning still let you update the row and then apply the change later?
Obnoxio The Clown wrote: > Lukas Barton said: >> Obnoxio The Clown wrote: >>> Lukas Barton said:
>>>> And how did you solve the problem I'am facing now: >>> Probably by using an index.
>> I am not so stupid not to use indexes.
> But are you smart enough to use the RIGHT index?
You might want to reconsider your question in light of the fact that in Oracle reads never block writes and writes never block reads. A feature SQL Server 2005 has duplicated.
If Informix want to complete with the market leaders it needs to have equivalent technology ... not a dwindling cadre legacy expert developers.
If you have to be "smart enough to use the RIGHT index" you are going to follow Ingres into oblivion. -- Daniel Morgan University of Washington
-----Original Message----- From: Mark Townsend <markbtowns...@comcast.net> To: informix-l...@iiug.org Date: Mon, 25 Sep 2006 06:14:17 -0700 Subject: Let me be the first
Daniel is right, and at the risk of sounding like an Obbie enthusiast....
Maybe the next Informix version should not be Cheetah, but rather Donkey.....
Informix should slow down and cater to the masses.... after all, it's not really about making a great database, it is about selling it. If we want to continue as a great database, we need to be satisfied with a smaller market share....
Now I will duck because surely the tomatoes will be coming....
[mailto:informix-list-boun...@iiug.org] On Behalf Of DA Morgan Sent: Monday, September 25, 2006 11:18 AM To: informix-l...@iiug.org Subject: Re: Let me be the first
Obnoxio The Clown wrote: > Lukas Barton said: >> Obnoxio The Clown wrote: >>> Lukas Barton said:
>>>> And how did you solve the problem I'am facing now: >>> Probably by using an index.
>> I am not so stupid not to use indexes.
> But are you smart enough to use the RIGHT index?
You might want to reconsider your question in light of the fact that in Oracle reads never block writes and writes never block reads. A feature SQL Server 2005 has duplicated.
If Informix want to complete with the market leaders it needs to have equivalent technology ... not a dwindling cadre legacy expert developers.
If you have to be "smart enough to use the RIGHT index" you are going to follow Ingres into oblivion. -- Daniel Morgan University of Washington _______________________________________________ Informix-list mailing list Informix-l...@iiug.org http://www.iiug.org/mailman/listinfo/informix-list ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================
>> It does not, because Informix locks also previous and next row in all >> indexes. So that the probability of locking rows that other user want to >> read is more than zero.
> I'm fascinated by this assertion. I guess I've just been unbelievably > lucky all my working life.
- http://docs.rinet.ru/InforSmes/ch15/ch15.htm ( For an index, three more locks are necessary--one lock for the accessed row, one for the row before the accessed row, and one for the row after the accessed row)
Sebastian, Norma J. wrote: > Daniel is right, and at the risk of sounding like an Obbie > enthusiast....
> Maybe the next Informix version should not be Cheetah, but rather > Donkey.....
Hee haw! :-)
> Informix should slow down and cater to the masses.... after all, it's > not really about making a great database, it is about selling it. If > we want to continue as a great database, we need to be satisfied with a > smaller market share....
> Now I will duck because surely the tomatoes will be coming....
You make great points. This is why MySQL is succeeding, despite its apparent deficiencies. In fact, most true MySQL shops are making it work, and adapting to how it *is* rather than what it should be. The whole premise surrounding MySQL is that for what it *does* have it works extremely well, which offsets the need for what it doesn't have. Again, why Informix SE should be running head-to-head against MySQL. It's very obvious to those of us that have used both products, but for some strange reason IBM Marketing just doesn't get it. Probably because they haven't used either enough to know.
> You might want to reconsider your question in light of the fact that > in Oracle reads never block writes and writes never block reads. A > feature SQL Server 2005 has duplicated.
> If Informix want to complete with the market leaders it needs to have > equivalent technology ... not a dwindling cadre legacy expert > developers.
> If you have to be "smart enough to use the RIGHT index" you are going > to follow Ingres into oblivion.
My words. I wanna use SQL via JDBC. I don't like to program in RPG, ... or write stored procedures. When I first came to programming for OS400 DB2 I meant there could be no more crazy database. (eg. query palnner does not use indexes when you OR two indexed columns in where condition (with the same query PostgreSQL, Oracle, Informix, Interbase, Firebird, PC DB2 and MS SQL does use them)). But recently I found one ..... eg. you cannot delete all rows from standard table (with loging) if the table is large...
>>> It does not, because Informix locks also previous and next row in all >>> indexes. So that the probability of locking rows that other user want to >>> read is more than zero.
>> I'm fascinated by this assertion. I guess I've just been unbelievably >> lucky all my working life.
When you change an index key value, you need a lock for the before copy to protect the prior key entry in the index. Otherwise, you could not roll back. Also, you need a lock for the after image - otherwise someone else could make the same change before you commit. These are not adjacent key locks which went away with version 5. It does NOT lock the previous and the next row.
Index locks are not done so much by locking a row. It is more like locking a value.
> - http://docs.rinet.ru/InforSmes/ch15/ch15.htm ( For an index, three > more locks are necessary--one lock for the accessed row, one for the row > before the accessed row, and one for the row after the accessed row)
This information is invalid since v6. With v5, we had something called adjacent key locking which was used to prevent someone from inserting an index item until the transaction which may have deleted that item had issued a commit. We changed that logic with version 6 with the addition of a 'delete' flag as part of the index item. That's what the b-tree cleaner is all about. We no longer lock the adjacent key.
One of the features in the version of IDS referred to in the article is, in fact, optimistic concurrency. On Sep 25, 2006, at 11:31 AM, Sebastian, Norma J. wrote:
> Daniel is right, and at the risk of sounding like an Obbie > enthusiast....
> Maybe the next Informix version should not be Cheetah, but rather > Donkey.....
> Informix should slow down and cater to the masses.... after all, it's > not really about making a great database, it is about selling it. If > we want to continue as a great database, we need to be satisfied > with a > smaller market share....
> Now I will duck because surely the tomatoes will be coming....
> -----Original Message----- > From: informix-list-boun...@iiug.org > [mailto:informix-list-boun...@iiug.org] On Behalf Of DA Morgan > Sent: Monday, September 25, 2006 11:18 AM > To: informix-l...@iiug.org > Subject: Re: Let me be the first
> Obnoxio The Clown wrote: >> Lukas Barton said: >>> Obnoxio The Clown wrote: >>>> Lukas Barton said:
>>>>> And how did you solve the problem I'am facing now: >>>> Probably by using an index.
>>> I am not so stupid not to use indexes.
>> But are you smart enough to use the RIGHT index?
> You might want to reconsider your question in light of the fact that > in Oracle reads never block writes and writes never block reads. A > feature SQL Server 2005 has duplicated.
> If Informix want to complete with the market leaders it needs to have > equivalent technology ... not a dwindling cadre legacy expert > developers.
> If you have to be "smart enough to use the RIGHT index" you are going > to follow Ingres into oblivion. > -- > Daniel Morgan > University of Washington > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > ============================================================ > The information contained in this message may be privileged > and confidential and protected from disclosure. If the reader > of this message is not the intended recipient, or an employee > or agent responsible for delivering this message to the > intended recipient, you are hereby notified that any reproduction, > dissemination or distribution of this communication is strictly > prohibited. If you have received this communication in error, > please notify us immediately by replying to the message and > deleting it from your computer. Thank you. Tellabs > ============================================================ > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list
|
