RE: question...
Yes, ids@iiug.org. Thanks, ***************************************************************************= *************** 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: Villagomez, Mary=20 Sent: Thursday, February 11, 2016 3:30 PM To: Knox, Ernest Subject: RE: question... Ernie, To submit a question, do you address the email to ids@iiug.org? I am having= trouble with the jvm/java install on the newer Kexe servers, and want to s= end in a question. Mary Villagomez Informix & MySQL DBA Member Technology Office: B2-260A Phone: 847-286-1768 -----Original Message----- From: Knox, Ernest Sent: Thursday, February 11, 2016 9:19 AM To: MySQLDBA Communications Subject: FW: Transaction Locking and Indexes [36547] Some Informix knowledge to retain. Thanks, ***************************************************************************= *************** Ernie Knox Sr. Technologist, I&TG - Database Management -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art K= agel Sent: Thursday, February 11, 2016 10:15 AM To: ids@iiug.org Subject: Re: Transaction Locking and Indexes [36547] David:=20 OK, here's what's happening:=20 Without the index every delete has to scan the entire table looking for mat= ching rows to delete. Each row as it is examined has to be locked, dirty re= ad or no dirty read, for a fraction of a second. As multiple scans zip thro= ugh the table it is inevitable that one or more will encounter a row that's= locked. If multiple sessions have to delete multiple rows on the same page= they will all have to latch the same page in the cache and so will queue u= p behind one another each holding a lock on a different row on that one pag= e. They will single thread on the cache page latch and on the LRU latch nee= ded to move the page from the clean part of the LRU queue to the dirty part= or from the middle of the dirty queue to the most recently used end of the= dirty queue. All that time the N+1st session is waiting for a row lock on = one of the rows in that frozen page to clear so it can read that row and co= ntinue its scan. Eventually, sometimes, the last waiter times out before it= gets access to the locks or latches that it needs.=20 This is one of the few cases in which page locks MIGHT perform better, but = I doubt it. The index almost eliminates the problem because that waiter who= doesn't need to update another row on the same cache page is able to skip = over the page locks because it is scanning the index instead which is not b= eing locked and which it does not have to acquire a lock for.=20 Art=20 Art S. Kagel, President and Principal Consultant ASK Database Management ww= w.askdbmgt.com=20 Blog: http://informix-myview.blogspot.com/=20 Disclaimer: Please keep in mind that my own opinions are my own opinions an= d do not reflect on the IIUG, nor any other organization with which I am as= sociated either explicitly, implicitly, or by inference. Neither do those o= pinions reflect those of other individuals affiliated with any entity with = which I am affiliated nor those of the entities themselves.=20 On Thu, Feb 11, 2016 at 9:54 AM, DAVID SIBLEY <d.sibley@bms.co.uk> wrote:=20 > Many thanks for the responses although the detail below may explain=20 > the issue a little better. >=20 > The database is in transnational mode. I have set lock mode to wait 30=20 > and the isolation level is dirty read. >=20 > The transaction running within the multiple copies of the application,=20 > one at each location should not clash as the select is unique to each=20 > site, the transactions should be unique. >=20 > The real question is why without an index on the column being used in=20 > the where clause do I get the error outlined and when I add the index=20 > on the column does the error cease and the delete statement executes succ= essfully. >=20 > Is there something in the database set-up which would cause this issue?=20 >=20 >=20 >=20 >=20 ***************************************************************************= ****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 >=20 >=20 --089e013a2268fd6bb7052b800208=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.
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 lock