IDS Forum

RE: question...

Posted By: Knox, Ernest
Date: Thursday, 11 February 2016, at 4:36 p.m.

In Response To: RE: question... (Knox, Ernest)

Sorry, please ignore. Was sending email address to co-worker.

Thanks,
*************************Sorry, please ignore.*****************************=
************************************
Ernie Knox
Sr. Technologist, I&TG - Database Management
Sears Holdings Management Corp
3333 Beverly Rd.
Hoffman Estates, IL. 60179
Office: (847) 286-5735
Email: Ernest.Knox@searshc.com
Cell: (847) 665-0722
Corp. Cell: 224-465-0553=A0
Corp. Text: 2244650553@txt.att.net
Informix Email: ifmxdba@searshc.com and Team: InformixDBA@searshc.com
Informix Primary: INFORMIXDBAPrimaryPager@searshc.com
Informix Secondary: INFORMIXDBASecondaryPager@searshc.com
MySQL Email: MYSQLDBAe@searshc.com and Team: MySQLDBA2@searshc.com
MySQL Primary: MYSQLDBAPrimaryPager@searshc.com
MySQL Secondary: MYSQLDBASecondaryPager@searshc.com

For more information, view our DBA Wiki page link below:
http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Managemen=
t#DatabaseManagement

For creating ServiceNow request, use the link below:
https://sears.service-now.com

For creating SUCCEED request, use the link below:
https://succeed.intra.searshc.com

Have I provided a WOW member experience today?
Send a Members' First Recognition to this Associate
Nominate this Associate for a Technology Excellence Award

My Area:
" Yes we can make a Change! "
" IF you can think it, you can do it! "
" Sometimes to secure the future, you have to let go of the past! "
" IT'S always a great day to watch=A0Sports: FOOTBALL "
GSU
MISSION:
" The focus of my life begins at home with family, loved ones, and friends.=
=A0 I want to use my resources to create a
secure environment that fosters love, learning, laughter, and mutual succes=
s.=A0 I will protect and value integrity.=A0 I
will admit and quickly correct my mistakes.=A0 I will be a self-starter.=A0=
I will be a caring person.=A0 I will be a good listener
with an open mind.=A0 I will continue to grow and learn.=A0 I will facilita=
te and celebrate the success of others. "
***************************************************************************=
***************

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Knox,=
Ernest
Sent: Thursday, February 11, 2016 4:34 PM
To: ids@iiug.org
Subject: RE: question... [36550]

Yes, ids@iiug.org.=20

Thanks,
***************************************************************************=
=3D
***************
Ernie Knox
Sr. Technologist, I&TG - Database Management Sears Holdings Management Corp
3333 Beverly Rd.=20
Hoffman Estates, IL. 60179
Office: (847) 286-5735
Email: Ernest.Knox@searshc.com
Cell: (847) 665-0722
Corp. Cell: 224-465-0553=3DA0
Corp. Text: 2244650553@txt.att.net
Informix Email: ifmxdba@searshc.com and Team: InformixDBA@searshc.com Infor=
mix Primary: INFORMIXDBAPrimaryPager@searshc.com
Informix Secondary: INFORMIXDBASecondaryPager@searshc.com
MySQL Email: MYSQLDBAe@searshc.com and Team: MySQLDBA2@searshc.com MySQL Pr=
imary: MYSQLDBAPrimaryPager@searshc.com MySQL Secondary: MYSQLDBASecondaryP=
ager@searshc.com=20

For more information, view our DBA Wiki page link below:=20
http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Managemen=
=3D
t#DatabaseManagement=20

For creating ServiceNow request, use the link below:=20
https://sears.service-now.com=20

For creating SUCCEED request, use the link below:=20
https://succeed.intra.searshc.com=20

Have I provided a WOW member experience today?=20
Send a Members' First Recognition to this Associate Nominate this Associate=
for a Technology Excellence Award=20

My Area:=20
" Yes we can make a Change! "=20
" IF you can think it, you can do it! "=20
" Sometimes to secure the future, you have to let go of the past! "=20
" IT'S always a great day to watch=3DA0Sports: FOOTBALL "=20
GSU
MISSION:=20
" The focus of my life begins at home with family, loved ones, and friends.=
=3D
=3DA0 I want to use my resources to create a secure environment that foster=
s love, learning, laughter, and mutual succes=3D
s.=3DA0 I will protect and value integrity.=3DA0 I will admit and quickly c=
orrect my mistakes.=3DA0 I will be a self-starter.=3DA0=3D I will be a cari=
ng person.=3DA0 I will be a good listener with an open mind.=3DA0 I will co=
ntinue to grow and learn.=3DA0 I will facilita=3D te and celebrate the succ=
ess of others. "=20
***************************************************************************=
=3D
***************=20

-----Original Message-----
From: Villagomez, Mary=3D20
Sent: Thursday, February 11, 2016 3:30 PM
To: Knox, Ernest
Subject: RE: question...=20

Ernie,
To submit a question, do you address the email to ids@iiug.org? I am having=
=3D trouble with the jvm/java install on the newer Kexe servers, and want t=
o s=3D end in a question.=20

Mary Villagomez
Informix & MySQL DBA
Member Technology
Office: B2-260A
Phone: 847-286-1768=20

-----Original Message-----
From: Knox, Ernest
Sent: Thursday, February 11, 2016 9:19 AM
To: MySQLDBA Communications
Subject: FW: Transaction Locking and Indexes [36547]=20

Some Informix knowledge to retain.=20

Thanks,
***************************************************************************=
=3D
***************
Ernie Knox
Sr. Technologist, I&TG - Database Management=20

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art K=
=3D agel
Sent: Thursday, February 11, 2016 10:15 AM
To: ids@iiug.org
Subject: Re: Transaction Locking and Indexes [36547]=20

David:=3D20=20

OK, here's what's happening:=3D20=20

Without the index every delete has to scan the entire table looking for mat=
=3D ching rows to delete. Each row as it is examined has to be locked, dirt=
y re=3D ad or no dirty read, for a fraction of a second. As multiple scans =
zip thro=3D ugh the table it is inevitable that one or more will encounter =
a row that's=3D locked. If multiple sessions have to delete multiple rows o=
n the same page=3D they will all have to latch the same page in the cache a=
nd so will queue u=3D p behind one another each holding a lock on a differe=
nt row on that one pag=3D e. They will single thread on the cache page latc=
h and on the LRU latch nee=3D ded to move the page from the clean part of t=
he LRU queue to the dirty part=3D or from the middle of the dirty queue to =
the most recently used end of the=3D dirty queue. All that time the N+1st s=
ession is waiting for a row lock on =3D one of the rows in that frozen page=
to clear so it can read that row and co=3D ntinue its scan. Eventually, so=
metimes, the last waiter times out before it=3D gets access to the locks or=
latches that it needs.=3D20=20

This is one of the few cases in which page locks MIGHT perform better, but =
=3D I doubt it. The index almost eliminates the problem because that waiter=
who=3D doesn't need to update another row on the same cache page is able t=
o skip =3D over the page locks because it is scanning the index instead whi=
ch is not b=3D eing locked and which it does not have to acquire a lock for=
.=3D20=20

Art=3D20=20

Art S. Kagel, President and Principal Consultant ASK Database Management ww=
=3D
w.askdbmgt.com=3D20=20

Blog: http://informix-myview.blogspot.com/=3D20=20

Disclaimer: Please keep in mind that my own opinions are my own opinions an=
=3D d do not reflect on the IIUG, nor any other organization with which I a=
m as=3D sociated either explicitly, implicitly, or by inference. Neither do=
those o=3D pinions reflect those of other individuals affiliated with any =
entity with =3D which I am affiliated nor those of the entities themselves.=
=3D20=20

On Thu, Feb 11, 2016 at 9:54 AM, DAVID SIBLEY <d.sibley@bms.co.uk> wrote:=
=3D20=20

> Many thanks for the responses although the detail below may explain=3D20=
=20=20
>the issue a little better.
>=3D20
> The database is in transnational mode. I have set lock mode to wait=20
>30=3D20 and the isolation level is dirty read.
>=3D20
> The transaction running within the multiple copies of the=20
>application,=3D20 one at each location should not clash as the select is=
=20
>unique to each=3D20 site, the transactions should be unique.
>=3D20
> The real question is why without an index on the column being used=20
>in=3D20 the where clause do I get the error outlined and when I add the=20
>index=3D20 on the column does the error cease and the delete statement=20
>executes succ=3D
essfully.=20
>=3D20
> Is there something in the database set-up which would cause this=20
>issue?=3D20
>=3D20
>=3D20
>=3D20
>=3D20
***************************************************************************=
=3D
****=3D20=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=3D20
>=3D20
>=3D20

--089e013a2268fd6bb7052b800208=3D20=20

***************************************************************************=
=3D
****
Forum Note: Use "Reply" to post a response in the discussion forum.=3D20=20

This message, including any attachments, is the property of Sears Holdings =
=3D Corporation and/or one of its subsidiaries. It is confidential and may =
cont=3D ain proprietary or legally privileged information. If you are not t=
he inten=3D ded recipient, please delete it without reading the contents. T=
hank you.=20

***************************************************************************=
****
Forum Note: Use "Reply" to post a response in the discussion forum.=20

This message, including any attachments, is the property of Sears Holdings =
Corporation and/or one of its subsidiaries. It is confidential and may cont=
ain proprietary or legally privileged information. If you are not the inten=
ded recipient, please delete it without reading the contents. Thank you.

Messages In This Thread

• RE: question...
  Knox, Ernest -- Thursday, 11 February 2016, at 4:33 p.m.
  • RE: question...
    Knox, Ernest -- Thursday, 11 February 2016, at 4:36 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.