Table Rows locking issue, how & Why?
Hello All,
I am having problems due to the rows locking issue, the problem is that with
the concurrency, that i have locking one row in a table but due to this other
rows in that table also gets locked, this is very critical, can you please let
me know what should be done???
i have creating a simple example to see the db locking issue and ran the
following sql query as:
-- In one db session, the following query is run,
-- that creates a test table and locks a record
create table test_locks(a int primary key, b varchar(50)) lock mode(row);
insert into test_locks(a,b) values(1,"abc");
insert into test_locks(a,b) values(2,"xyz");
insert into test_locks(a,b) values(3,"abcxyz");
-- to lock the 1st row
begin work;
update test_locks set b=b where a=1;
--------------------------------------------------------
-- In 2nd db session, following query to fetch a different record,
-- which not locked but this is still giving the locking error, why?
select * from test_locks
where a = 2
-- Although, the record with a=2 should not be locked but it is still
-- giving the locking error as follows, why?
244: Could not do a physical-order read to fetch next row.
107: ISAM error: record is locked
For further information, i would like to let you know i am using IDS 10.0 FC4
& this database is created in the "Unbuffered Logging Mode" & isolation level
is "commited read"
Thanks in advance for the anticipation.
Regards,
Omer Saeed Khan
=
"OMER KHAN" =
<oskhan@i2cinc.co =
m> =
To
Sent by: ids@iiug.org =
ids-bounces@iiug. =
cc
org =
Subj=
ect
Table Rows locking issue, how & =
08/01/2007 05:36 Why? [9665] =
AM =
=
=
Please respond to =
ids@iiug.org =
=
=
Hello All,
I am having problems due to the rows locking issue, the problem is that=
with
the concurrency, that i have locking one row in a table but due to this=
other
rows in that table also gets locked, this is very critical, can you ple=
ase
let
me know what should be done???
i have creating a simple example to see the db locking issue and ran th=
e
following sql query as:
-- In one db session, the following query is run,
-- that creates a test table and locks a record
create table test_locks(a int primary key, b varchar(50)) lock mode(row=
);
insert into test_locks(a,b) values(1,"abc");
insert into test_locks(a,b) values(2,"xyz");
insert into test_locks(a,b) values(3,"abcxyz");
-- to lock the 1st row
begin work;
update test_locks set b=3Db where a=3D1;
--------------------------------------------------------
-- In 2nd db session, following query to fetch a different record,
-- which not locked but this is still giving the locking error, why?
Because when a table is very small, the optimizer will attempt to do a
sequential scan (one physical IO) rather than using an index. (two
physical IO's). The scan will run into the row where a =3D 1 prior to =
seeing
the row where a =3D 2, and thus will run into the update lock on that r=
ow.
To avoid this, you will need to use optimizer hints to force the fetch =
to
be done by using indexes, and or migrate to IDS 11 and use last committ=
ed
reads.
select * from test_locks
where a =3D 2
-- Although, the record with a=3D2 should not be locked but it is still=
-- giving the locking error as follows, why?
244: Could not do a physical-order read to fetch next row.
107: ISAM error: record is locked
For further information, i would like to let you know i am using IDS 10=
.0
FC4
& this database is created in the "Unbuffered Logging Mode" & isolation=
level
is "commited read"
Thanks in advance for the anticipation.
Regards,
Omer Saeed Khan
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
On 8/1/07, Madison Pruet <mpruet@us.ibm.com> wrote:
> =
>
> "OMER KHAN" =
>
> <oskhan@i2cinc.co =
>
> m> =
> To
>
> Sent by: ids@iiug.org =
>
> ids-bounces@iiug. =
> cc
>
> org =
>
> Subj=
> ect
>
> Table Rows locking issue, how & =
>
> 08/01/2007 05:36 Why? [9665] =
>
> AM =
>
> =
>
> =
>
> Please respond to =
>
> ids@iiug.org =
>
> =
>
> =
>
> Hello All,
>
> I am having problems due to the rows locking issue, the problem is that=
>
> with
> the concurrency, that i have locking one row in a table but due to this=
>
> other
> rows in that table also gets locked, this is very critical, can you ple=
> ase
> let
> me know what should be done???
>
> i have creating a simple example to see the db locking issue and ran th=
> e
> following sql query as:
>
> -- In one db session, the following query is run,
> -- that creates a test table and locks a record
>
> create table test_locks(a int primary key, b varchar(50)) lock mode(row=
> );
> insert into test_locks(a,b) values(1,"abc");
> insert into test_locks(a,b) values(2,"xyz");
> insert into test_locks(a,b) values(3,"abcxyz");>
> -- to lock the 1st row
> begin work;
> update test_locks set b=3Db where a=3D1;>
> --------------------------------------------------------
>
> -- In 2nd db session, following query to fetch a different record,
> -- which not locked but this is still giving the locking error, why?
>
> Because when a table is very small, the optimizer will attempt to do a
> sequential scan (one physical IO) rather than using an index. (two
> physical IO's). The scan will run into the row where a =3D 1 prior to =
> seeing
> the row where a =3D 2, and thus will run into the update lock on that r=
> ow.
> To avoid this, you will need to use optimizer hints to force the fetch =
> to
> be done by using indexes, and or migrate to IDS 11 and use last committ=
> ed
> reads.
>
> select * from test_locks
> where a =3D 2>
> -- Although, the record with a=3D2 should not be locked but it is still=
>
> -- giving the locking error as follows, why?
>
> 244: Could not do a physical-order read to fetch next row.
> 107: ISAM error: record is locked>
> For further information, i would like to let you know i am using IDS 10=
> ..0
> FC4
> & this database is created in the "Unbuffered Logging Mode" & isolation=
>
> level
> is "commited read"
>
> Thanks in advance for the anticipation.
>
> Regards,
> Omer Saeed Khan
>
> ***********************************************************************=
> ********
This issue is a rather annoying one and one that I'm surprised has
been addressed yet. And no, I don't think that the new 'commited
read' is the best way to address this, don't get me wrong, this is a
great new feature but not one to address this particular issue. I
suggest that this should be fixed within the engine; that perhaps if
the engine sees that using a sequential scan may cause a locking error
that it should fall back to use the index instead, automatically.
Another work around if you can't modify you application is to fudge
your statistics; stuff your table full of data, update your
statistics, and then delete your bogus data.
Regards,
Jim
Use the optimizer directive to force the use of index.
Example:
SELECT {+INDEX(emp idx_dept_no)} * from emp where dept_no=10;
where emp is the table having column dept_no
and idx_dept_no is the index on column dept_no
For more information refer to:
http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.s
qls.doc/sqls1224.htm
"Jim Kenedy" <jimkenedy@gmail.com>
Sent by: ids-bounces@iiug.org
08/01/2007 08:25 AM
Please respond to
ids@iiug.org
To
ids@iiug.org
cc
Subject
Re: Table Rows locking issue, how & Why? [9667]
On 8/1/07, Madison Pruet <mpruet@us.ibm.com> wrote:
> =
>
> "OMER KHAN" =
>
> <oskhan@i2cinc.co =
>
> m> =
> To
>
> Sent by: ids@iiug.org =
>
> ids-bounces@iiug. =
> cc
>
> org =
>
> Subj=
> ect
>
> Table Rows locking issue, how & =
>
> 08/01/2007 05:36 Why? [9665] =
>
> AM =
>
> =
>
> =
>
> Please respond to =
>
> ids@iiug.org =
>
> =
>
> =
>
> Hello All,
>
> I am having problems due to the rows locking issue, the problem is that=
>
> with
> the concurrency, that i have locking one row in a table but due to this=
>
> other
> rows in that table also gets locked, this is very critical, can you ple=
> ase
> let
> me know what should be done???
>
> i have creating a simple example to see the db locking issue and ran th=
> e
> following sql query as:
>
> -- In one db session, the following query is run,
> -- that creates a test table and locks a record
>
> create table test_locks(a int primary key, b varchar(50)) lock mode(row=
> );
> insert into test_locks(a,b) values(1,"abc");
> insert into test_locks(a,b) values(2,"xyz");
> insert into test_locks(a,b) values(3,"abcxyz");>
> -- to lock the 1st row
> begin work;
> update test_locks set b=3Db where a=3D1;>
> --------------------------------------------------------
>
> -- In 2nd db session, following query to fetch a different record,
> -- which not locked but this is still giving the locking error, why?
>
> Because when a table is very small, the optimizer will attempt to do a
> sequential scan (one physical IO) rather than using an index. (two
> physical IO's). The scan will run into the row where a =3D 1 prior to =
> seeing
> the row where a =3D 2, and thus will run into the update lock on that r=
> ow.
> To avoid this, you will need to use optimizer hints to force the fetch =
> to
> be done by using indexes, and or migrate to IDS 11 and use last committ=
> ed
> reads.
>
> select * from test_locks
> where a =3D 2>
> -- Although, the record with a=3D2 should not be locked but it is still=
>
> -- giving the locking error as follows, why?
>
> 244: Could not do a physical-order read to fetch next row.
> 107: ISAM error: record is locked>
> For further information, i would like to let you know i am using IDS 10=
> ..0
> FC4
> & this database is created in the "Unbuffered Logging Mode" & isolation=
>
> level
> is "commited read"
>
> Thanks in advance for the anticipation.
>
> Regards,
> Omer Saeed Khan
>
> ***********************************************************************=
> ********
This issue is a rather annoying one and one that I'm surprised has
been addressed yet. And no, I don't think that the new 'commited
read' is the best way to address this, don't get me wrong, this is a
great new feature but not one to address this particular issue. I
suggest that this should be fixed within the engine; that perhaps if
the engine sees that using a sequential scan may cause a locking error
that it should fall back to use the index instead, automatically.
Another work around if you can't modify you application is to fudge
your statistics; stuff your table full of data, update your
statistics, and then delete your bogus data.
Regards,
Jim
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Somehow, I saw a blank comments from Madison.
Thanks,
Frank
On 8/1/07, Madison Pruet <mpruet@us.ibm.com> wrote:
>
> =
>
> "OMER KHAN" =
>
> <oskhan@i2cinc.co =
>
> m> =
> To
>
> Sent by: ids@iiug.org =
>
> ids-bounces@iiug. =
> cc
>
> org =
>
> Subj=
> ect
>
> Table Rows locking issue, how & =
>
> 08/01/2007 05:36 Why? [9665] =
>
> AM =
>
> =
>
> =
>
> Please respond to =
>
> ids@iiug.org =
>
> =
>
> =
>
> Hello All,
>
> I am having problems due to the rows locking issue, the problem is that=
>
> with
> the concurrency, that i have locking one row in a table but due to this=
>
> other
> rows in that table also gets locked, this is very critical, can you ple=
> ase
> let
> me know what should be done???
>
> i have creating a simple example to see the db locking issue and ran th=
> e
> following sql query as:
>
> -- In one db session, the following query is run,
> -- that creates a test table and locks a record
>
> create table test_locks(a int primary key, b varchar(50)) lock mode(row=
> );
> insert into test_locks(a,b) values(1,"abc");
> insert into test_locks(a,b) values(2,"xyz");
> insert into test_locks(a,b) values(3,"abcxyz");>
> -- to lock the 1st row
> begin work;
> update test_locks set b=3Db where a=3D1;>
> --------------------------------------------------------
>
> -- In 2nd db session, following query to fetch a different record,
> -- which not locked but this is still giving the locking error, why?
>
> Because when a table is very small, the optimizer will attempt to do a
> sequential scan (one physical IO) rather than using an index. (two
> physical IO's). The scan will run into the row where a =3D 1 prior to =
> seeing
> the row where a =3D 2, and thus will run into the update lock on that r=
> ow.
> To avoid this, you will need to use optimizer hints to force the fetch =
> to
> be done by using indexes, and or migrate to IDS 11 and use last committ=
> ed
> reads.
>
> select * from test_locks
> where a =3D 2>
> -- Although, the record with a=3D2 should not be locked but it is still=
>
> -- giving the locking error as follows, why?
>
> 244: Could not do a physical-order read to fetch next row.
> 107: ISAM error: record is locked>
> For further information, i would like to let you know i am using IDS 10=
> ..0
> FC4
> & this database is created in the "Unbuffered Logging Mode" & isolation=
>
> level
> is "commited read"
>
> Thanks in advance for the anticipation.
>
> Regards,
> Omer Saeed Khan
>
> ***********************************************************************=
> ********
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> =
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>