RE: dirty reada to committed read

Premnath- Have you looked at the table schema to see what lock mode is set: dbschema -d mydatabase -t mytable -ss If there is no possibility of overlap, setting the lock mode to row will do the trick. --EEM -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Premnath Srinivasan Sent: Thursday, March 08, 2007 11:17 AM To: informix-list@iiug.org Subject: Re: dirty reada to committed read I have two users trying to update rows in the same table. Each user is going after a distinct set of rows: for example, user1 updates where customer_num = 10, user2 updates where customer_num = 20; there is no possibility that the users are attempting to update the same row at the same time. But they are getting locking errors. Is this Adjacent Key Locking? I have tried setting Isolation Level to Dirty Read, but it still doesn't work. First of all, it is important to realize that Dirty Read affects only queries, not updates and deletes. It is not possible to do a "Dirty Update"; that is, you cannot choose to ignore a locked row when doing an update. To do that, you would have to declare a simple cursor using Dirty Read, and then update individual rows based on the primary key, ignoring any lock errors, like so:  WHENEVER ERROR CONTINUE  -- because the UPDATE will fail on a lock  SET ISOLATION DIRTY READ DECLARE c1 CURSOR FOR SELECT unique-key WHERE { whatever your criteria are }  FOREACH c1 INTO x  UPDATE table WHERE unique-key = x  END FOREACH The only isolation level that has an impact on updates and deletes is Repeatable Read (more on this later); otherwise isolation level on updates and deletes is Committed Read. Next, think about how you are accessing the rows. Keep in mind that if the row is locked, the engine cannot read it, even just to determine whether it meets your criteria or not. Therefore, if any row which your session needs to read is locked, your query will fail with a lock error. The way to avoid a problem is to use an index to go directly to the row(s) you need. If your query uses a sequential scan, it will eventually fail if even one row in the table is locked, regardless of what that one row is. Also, be aware that even if your query uses an index, if your index cannot isolate the row you want, you will have to scan the rows returned by the index, which will fail if any of these rows are locked. It is very important to make sure that your queries use the best access path, which usually means an index which takes you directly to your row, and not requiring reading any other rows. Ex.1. User1 updates rows where customer_num = 10. User2 attempts to update rows where customer_num = 20. Table has no indexes, therefore user2 does a sequential scan, and fails when he reaches customer_num 10, which is locked by user1. Although user1's row contains customer_num 10, not 20, the engine cannot verify that the row does not meet the criteria, because it is locked. If there were an index on customer_num, user2 could use the index to go directly to customer_num 20, never having to read the row locked by user1. Ex.2. User1 updates rows for city="Menlo Park", state="CA". User2 attempts to update rows for city="San Francisco", state="CA". State is indexed. User2 reads the index on state, which shows three rows with state="CA". User2 must then read the data rows pointed to by this index, and gets an error when he reaches the Menlo Park row locked by User1. If the index were on (state, city), user2 could go directly to the "San Francisco" row, and never try to read the row locked by user1. So, how does Repeatable Read affect updates? The definition of Repeatable Read is that if the user were to re-run the same query/update/delete within that transaction, the same results would be returned. That is, data cannot be changed in any of the rows that were selected, nor can any more rows be added that would fit the criteria of the query/update/delete. For example, if I update all rows where customer_num = 10, then until I commit the transaction, not only can you not update any of those rows, but you cannot insert any new rows with customer_num = 10. This means that the adjacent index item must be locked to prevent you inserting this new row; this is very similar to Adjacent Key Locking. Hope this gives you some idea. "Art S. Kagel" <kagel@bloomberg. net> To Sent by: informix-list@iiug.org informix-list-bou cc nces@iiug.org Subject Re: dirty reada to committed read 08/03/2007 21:43 eferreyra wrote: WOrks for me.... Though I don't know what it has to do with your question? Art S. Kagel > appears to be a dead link... > > On Mar 8, 2:43 am, Premnath Srinivasan <s.premn...@in.ibm.com> wrote: > >>Hi, >> >>Refer the follwing link. >> >>http://www.iiug.org/waiug/archive/iugnew84.html >> >>Regards, >>Premnath Srinivasan >> >> "eferreyra" >> <eferreyra@gmail. >> com> To >> Sent by: informix-l...@iiug.org >> informix-list-bou cc >> n...@iiug.org >> Subject >> dirty reada to committed read >> 08/03/2007 01:12 >> >>Hi, i start a program that uses ODBC for connect to an informix (7) >>IDS, i start the connection with isolation level of dirty read, but in >>some point checking the SMI tables i see connection have isolation >>level of committed read. >> >>I dont change the level via set isolation level to... how can this >>happen ? >> >>Some idea ? >> >>Thanks >> >>A.U.S. Enrique Ferreyra >> >>_______________________________________________ >>Informix-list mailing list >>Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list > > > _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

I pleny understan the concepts of isolation level, lock mode, etc. just want to know if were possible that informix changes my isolation level without my instruction to do it . My problem was in the ODBC layer, found it. Thanks for the help! A.U.S. Enrique Ferreyra On Mar 8, 2:21 pm, "Everett Mills" <eemi...@nationalbeef.com> wrote: > Premnath- > Have you looked at the table schema to see what lock mode is set: > > dbschema -d mydatabase -t mytable -ss> > If there is no possibility of overlap, setting the lock mode to row will do the trick. > > --EEM > > -----Original Message----- > From: informix-list-boun...@iiug.org [mailto:informix-list-boun...@iiug.org] On Behalf Of Premnath Srinivasan > Sent: Thursday, March 08, 2007 11:17 AM > To: informix-l...@iiug.org > Subject: Re: dirty reada to committed read > > I have two users trying to update rows in the same table. Each user is > going after a distinct set of rows: for example, user1 updates where > customer_num = 10, user2 updates where customer_num = 20; there is no > possibility that the users are attempting to update the same row at the > same time. But they are getting locking errors. Is this Adjacent Key > Locking? I have tried setting Isolation Level to Dirty Read, but it still > doesn't work. > > First of all, it is important to realize that Dirty Read affects only > queries, not updates and deletes. It is not possible to do a "Dirty > Update"; that is, you cannot choose to ignore a locked row when doing an > update. To do that, you would have to declare a simple cursor using Dirty > Read, and then update individual rows based on the primary key, ignoring > any lock errors, like so: > > WHENEVER ERROR CONTINUE -- because the UPDATE will fail on a lock > SET ISOLATION DIRTY READ > DECLARE c1 CURSOR FOR SELECT unique-key WHERE { whatever your criteria are> } > FOREACH c1 INTO x > UPDATE table WHERE unique-key = x > END FOREACH > > The only isolation level that has an impact on updates and deletes is > Repeatable Read (more on this later); otherwise isolation level on updates > and deletes is Committed Read. > Next, think about how you are accessing the rows. Keep in mind that if the > row is locked, the engine cannot read it, even just to determine whether it > meets your criteria or not. Therefore, if any row which your session needs > to read is locked, your query will fail with a lock error. The way to avoid > a problem is to use an index to go directly to the row(s) you need. If your > query uses a sequential scan, it will eventually fail if even one row in > the table is locked, regardless of what that one row is. Also, be aware > that even if your query uses an index, if your index cannot isolate the row > you want, you will have to scan the rows returned by the index, which will > fail if any of these rows are locked. It is very important to make sure > that your queries use the best access path, which usually means an index > which takes you directly to your row, and not requiring reading any other > rows. > > Ex.1. User1 updates rows where customer_num = 10. User2 attempts to update > rows where customer_num = 20. Table has no indexes, therefore user2 does a > sequential scan, and fails when he reaches customer_num 10, which is locked > by user1. Although user1's row contains customer_num 10, not 20, the engine > cannot verify that the row does not meet the criteria, because it is > locked. If there were an index on customer_num, user2 could use the index > to go directly to customer_num 20, never having to read the row locked by > user1. > > Ex.2. User1 updates rows for city="Menlo Park", state="CA". User2 attempts > to update rows for city="San Francisco", state="CA". State is indexed. > User2 reads the index on state, which shows three rows with state="CA". > User2 must then read the data rows pointed to by this index, and gets an > error when he reaches the Menlo Park row locked by User1. If the index were > on (state, city), user2 could go directly to the "San Francisco" row, and > never try to read the row locked by user1. > So, how does Repeatable Read affect updates? The definition of Repeatable > Read is that if the user were to re-run the same query/update/delete within > that transaction, the same results would be returned. That is, data cannot > be changed in any of the rows that were selected, nor can any more rows be > added that would fit the criteria of the query/update/delete. For example, > if I update all rows where customer_num = 10, then until I commit the > transaction, not only can you not update any of those rows, but you cannot > insert any new rows with customer_num = 10. This means that the adjacent > index item must be locked to prevent you inserting this new row; this is > very similar to Adjacent Key Locking. > > Hope this gives you some idea. > > "Art S. Kagel" > <kagel@bloomberg. > net> To > Sent by: informix-l...@iiug.org > informix-list-bou cc > n...@iiug.org > Subject > Re: dirty reada to committed read > 08/03/2007 21:43 > > eferreyra wrote: > > WOrks for me.... Though I don't know what it has to do with your question? > > Art S. Kagel > > > appears to be a dead link... > > > On Mar 8, 2:43 am, Premnath Srinivasan <s.premn...@in.ibm.com> wrote: > > >>Hi, > > >>Refer the follwing link. > > >>http://www.iiug.org/waiug/archive/iugnew84.html > > >>Regards, > >>Premnath Srinivasan > > >> "eferreyra" > >> <eferreyra@gmail. > >> com> > To > >> Sent by: informix-l...@iiug.org > >> informix-list-bou > cc > >> n...@iiug.org > > Subject > >> dirty reada to committed read > >> 08/03/2007 01:12 > > >>Hi, i start a program that uses ODBC for connect to an informix (7) > >>IDS, i start the connection with isolation level of dirty read, but in > >>some point checking the SMI tables i see connection have isolation > >>level of committed read. > > >>I dont change the level via set isolation level to... how can this > >>happen ? > > >>Some idea ? > > >>Thanks > > >>A.U.S. Enrique Ferreyra > > >>_______________________________________________ > >>Informix-list mailing list > >>Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list > > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list > > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug