RE: dirty reada to committed read
- From: "Chafik, Basim" <Basim.Chafik@xxxxxxxxxx>
- Date: Thu, 8 Mar 2007 12:17:11 -0500
It is also possible that the table lock level is set to (P)age rather than
(R)ow. In this case if the 2 rows of customer num 10 and 20 are in the same
page then user1 is locking the whole page therefore user2 get a lock error
when trying to update another row but from the same page
Basim Chafik
Senior Systems Analyst
IBM Certified Advanced Technical Expert (CATE)
1.800.688.4895
basim.chafik@xxxxxxx
plexus (Division of BancTec)
-----Original Message-----
From: Premnath Srinivasan [mailto:s.premnath@xxxxxxxxxx]
Sent: Thursday, March 08, 2007 12:17 PM
To: informix-list@xxxxxxxx
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@xxxxxxxx
informix-list-bou cc
nces@xxxxxxxx
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...To
On Mar 8, 2:43 am, Premnath Srinivasan <s.premn...@xxxxxxxxxx> wrote:
Hi,
Refer the follwing link.
http://www.iiug.org/waiug/archive/iugnew84.html
Regards,
Premnath Srinivasan
"eferreyra"
<eferreyra@gmail.
com>
ccSent by: informix-l...@xxxxxxxx
informix-list-bou
Subjectn...@xxxxxxxx
_______________________________________________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...@xxxxxxxxxxxx://www.iiug.org/mailman/listinfo/informix-list
Informix-list mailing list
Informix-list@xxxxxxxx
http://www.iiug.org/mailman/listinfo/informix-list
_______________________________________________
Informix-list mailing list
Informix-list@xxxxxxxx
http://www.iiug.org/mailman/listinfo/informix-list
.
- Prev by Date: Re: dirty reada to committed read
- Next by Date: RE: dirty reada to committed read
- Previous by thread: Re: dirty reada to committed read
- Next by thread: RE: dirty reada to committed read
- Index(es):
Relevant Pages
|
