Dead lock detected

Hi, Since about 3 weeks a go, there had been an increment of dead locks in the database server and the users say the system is more slow than before. What can I do to reduce the dead locks and increase the system response ? Thanks in advance. --Apple-Mail-112EA2CF-C317-4221-9E31-9D8F13E86339

The only way to eliminate deadlocks is to be disciplines when developing your applications. Always access table A before table B in a transaction. Deadlocks happen when two users need access to two or more of the same resources and lock then in different orders so that each user acquires a lock on one of these resources thereby locking the other out. Fix your applications. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 21, 2016 at 9:27 AM, Jorge Valenzuela <jorgervt@gmail.com> wrote: > Hi, > Since about 3 weeks a go, there had been an increment of dead locks in the > database server and the users say the system is more slow than before. What > can I do to reduce the dead locks and increase the system response ? > Thanks in advance. > --Apple-Mail-112EA2CF-C317-4221-9E31-9D8F13E86339 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1144aa2ed362ce0541d092b5

Dead locks are caused by an application problem. Did you release new code? Or, did you business change such that many users are trying to access the same records? On the database side, the main thing to do is make sure all your tables are created to use row level locking mode. Monitor and find the sessions that cause the deadlocks and then exam the application logic. Regards - Lester On 11/21/16 9:27 AM, Jorge Valenzuela wrote: > Hi, > Since about 3 weeks a go, there had been an increment of dead locks in the > database server and the users say the system is more slow than before. What > can I do to reduce the dead locks and increase the system response ? > Thanks in advance. > --Apple-Mail-112EA2CF-C317-4221-9E31-9D8F13E86339 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- ______________________________________________________________________ Lester Knutsen lester@advancedatatools.com Advanced DataTools Corporation Voice: 703-256-0267 x102 Visit our Web page: http://www.advancedatatools.com ______________________________________________________________________

Thanks for your help. By default page level locking is used by Informix and I never change this, will it help if I change to row level locking or there are another disventages to do this ? > El 21/11/2016, a las 06:36, Art Kagel <art.kagel@gmail.com> escribió: > > The only way to eliminate deadlocks is to be disciplines when developing > your applications. Always access table A before table B in a transaction. > Deadlocks happen when two users need access to two or more of the same > resources and lock then in different orders so that each user acquires a > lock on one of these resources thereby locking the other out. > > Fix your applications. > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Mon, Nov 21, 2016 at 9:27 AM, Jorge Valenzuela <jorgervt@gmail.com> > wrote: > >> Hi, >> Since about 3 weeks a go, there had been an increment of dead locks in the >> database server and the users say the system is more slow than before. What >> can I do to reduce the dead locks and increase the system response ? >> Thanks in advance. >> --Apple-Mail-112EA2CF-C317-4221-9E31-9D8F13E86339 >> >> >> ************************************************************ >> ******************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --001a1144aa2ed362ce0541d092b5 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Thanks, onstat -u tell me how many locks does any session had, but which command tell me which table/record are the session locked ? > El 21/11/2016, a las 06:56, Lester Knutsen <lester@advancedatatools.com> escribió: > > Dead locks are caused by an application problem. Did you release new code? Or, > did you business change such that many users are trying to access the same > records? On the database side, the main thing to do is make sure all your > tables are created to use row level locking mode. Monitor and find the > sessions that cause the deadlocks and then exam the application logic. > > Regards - Lester > >> On 11/21/16 9:27 AM, Jorge Valenzuela wrote: >> Hi, >> Since about 3 weeks a go, there had been an increment of dead locks in the >> database server and the users say the system is more slow than before. What >> can I do to reduce the dead locks and increase the system response ? >> Thanks in advance. >> --Apple-Mail-112EA2CF-C317-4221-9E31-9D8F13E86339 >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > -- > ______________________________________________________________________ > Lester Knutsen lester@advancedatatools.com > Advanced DataTools Corporation Voice: 703-256-0267 x102 > Visit our Web page: http://www.advancedatatools.com > ______________________________________________________________________ > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Row locking is preferred for any OLTP environment, page locking for Data Warehouse. There is slightly more overhead and the engine will assign more memory to locks, but the reduction in lock contention normally more than makes up for the added lock overhead. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 21, 2016 at 4:10 PM, Jorge Valenzuela <jorgervt@gmail.com> wrote: > Thanks for your help. > By default page level locking is used by Informix and I never change this, > will it help if I change to row level locking or there are another > disventages > to do this ? > > > El 21/11/2016, a las 06:36, Art Kagel <art.kagel@gmail.com> escribió: > > > > The only way to eliminate deadlocks is to be disciplines when developing > > your applications. Always access table A before table B in a transaction. > > Deadlocks happen when two users need access to two or more of the same > > resources and lock then in different orders so that each user acquires a > > lock on one of these resources thereby locking the other out. > > > > Fix your applications. > > > > Art > > > > Art S. Kagel, President and Principal Consultant > > ASK Database Management > > www.askdbmgt.com > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on the IIUG, nor any other organization with which I > am > > associated either explicitly, implicitly, or by inference. Neither do > > those opinions reflect those of other individuals affiliated with any > > entity with which I am affiliated nor those of the entities themselves. > > > > On Mon, Nov 21, 2016 at 9:27 AM, Jorge Valenzuela <jorgervt@gmail.com> > > wrote: > > > >> Hi, > >> Since about 3 weeks a go, there had been an increment of dead locks in > the > >> database server and the users say the system is more slow than before. > What > >> can I do to reduce the dead locks and increase the system response ? > >> Thanks in advance. > >> --Apple-Mail-112EA2CF-C317-4221-9E31-9D8F13E86339 > >> > >> > >> ************************************************************ > >> ******************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > > > > --001a1144aa2ed362ce0541d092b5 > > > > > > > ************************************************************ > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114c9cccccca380541d62a18

onstat -k describes the locks and what session is holding them (match theowner column in onstat -k to the address column in onstat -u to find the session id holding the locks. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 21, 2016 at 4:13 PM, Jorge Valenzuela <jorgervt@gmail.com> wrote: > Thanks, onstat -u tell me how many locks does any session had, but which > command tell me which table/record are the session locked ? > > > El 21/11/2016, a las 06:56, Lester Knutsen <lester@advancedatatools.com> > escribió: > > > > Dead locks are caused by an application problem. Did you release new > code? > Or, > > did you business change such that many users are trying to access the > same > > records? On the database side, the main thing to do is make sure all your > > tables are created to use row level locking mode. Monitor and find the > > sessions that cause the deadlocks and then exam the application logic. > > > > Regards - Lester > > > >> On 11/21/16 9:27 AM, Jorge Valenzuela wrote: > >> Hi, > >> Since about 3 weeks a go, there had been an increment of dead locks in > the > >> database server and the users say the system is more slow than before. > What > >> can I do to reduce the dead locks and increase the system response ? > >> Thanks in advance. > >> --Apple-Mail-112EA2CF-C317-4221-9E31-9D8F13E86339 > >> > >> > >> > > > ************************************************************ > ******************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > > > > -- > > ______________________________________________________________________ > > Lester Knutsen lester@advancedatatools.com > > Advanced DataTools Corporation Voice: 703-256-0267 x102 > > Visit our Web page: http://www.advancedatatools.com > > ______________________________________________________________________ > > > > > > > ************************************************************ > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113f7776b684d10541d62f7c

You may find my "ixlocks" script useful: https://github.com/domusonline/InformixScripts/tree/master/scripts/ix In case of any issue with it please report. On Mon, Nov 21, 2016 at 10:13 PM, Jorge Valenzuela <jorgervt@gmail.com> wrote: > Thanks, onstat -u tell me how many locks does any session had, but which > command tell me which table/record are the session locked ? > > > El 21/11/2016, a las 06:56, Lester Knutsen <lester@advancedatatools.com> > escribió: > > > > Dead locks are caused by an application problem. Did you release new > code? > Or, > > did you business change such that many users are trying to access the > same > > records? On the database side, the main thing to do is make sure all your > > tables are created to use row level locking mode. Monitor and find the > > sessions that cause the deadlocks and then exam the application logic. > > > > Regards - Lester > > > >> On 11/21/16 9:27 AM, Jorge Valenzuela wrote: > >> Hi, > >> Since about 3 weeks a go, there had been an increment of dead locks in > the > >> database server and the users say the system is more slow than before. > What > >> can I do to reduce the dead locks and increase the system response ? > >> Thanks in advance. > >> --Apple-Mail-112EA2CF-C317-4221-9E31-9D8F13E86339 > >> > >> > >> > > > ************************************************************ > ******************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > > > > -- > > ______________________________________________________________________ > > Lester Knutsen lester@advancedatatools.com > > Advanced DataTools Corporation Voice: 703-256-0267 x102 > > Visit our Web page: http://www.advancedatatools.com > > ______________________________________________________________________ > > > > > > > ************************************************************ > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a114396e44741b10541d6402d

Thank you, for all your help, I will talk with the developers and I will tests changing from page to row locking. > El 21/11/2016, a las 13:16, Art Kagel <art.kagel@gmail.com> escribió: > > Row locking is preferred for any OLTP environment, page locking for Data > Warehouse. There is slightly more overhead and the engine will assign more > memory to locks, but the reduction in lock contention normally more than > makes up for the added lock overhead. > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Mon, Nov 21, 2016 at 4:10 PM, Jorge Valenzuela <jorgervt@gmail.com> > wrote: > >> Thanks for your help. >> By default page level locking is used by Informix and I never change this, >> will it help if I change to row level locking or there are another >> disventages >> to do this ? >> >>> El 21/11/2016, a las 06:36, Art Kagel <art.kagel@gmail.com> escribió: >>> >>> The only way to eliminate deadlocks is to be disciplines when developing >>> your applications. Always access table A before table B in a transaction. >>> Deadlocks happen when two users need access to two or more of the same >>> resources and lock then in different orders so that each user acquires a >>> lock on one of these resources thereby locking the other out. >>> >>> Fix your applications. >>> >>> Art >>> >>> Art S. Kagel, President and Principal Consultant >>> ASK Database Management >>> www.askdbmgt.com >>> >>> Blog: http://informix-myview.blogspot.com/ >>> >>> Disclaimer: Please keep in mind that my own opinions are my own opinions >>> and do not reflect on the IIUG, nor any other organization with which I >> am >>> associated either explicitly, implicitly, or by inference. Neither do >>> those opinions reflect those of other individuals affiliated with any >>> entity with which I am affiliated nor those of the entities themselves. >>> >>> On Mon, Nov 21, 2016 at 9:27 AM, Jorge Valenzuela <jorgervt@gmail.com> >>> wrote: >>> >>>> Hi, >>>> Since about 3 weeks a go, there had been an increment of dead locks in >> the >>>> database server and the users say the system is more slow than before. >> What >>>> can I do to reduce the dead locks and increase the system response ? >>>> Thanks in advance. >>>> --Apple-Mail-112EA2CF-C317-4221-9E31-9D8F13E86339 >>>> >>>> >>>> ************************************************************ >>>> ******************* >>>> Forum Note: Use "Reply" to post a response in the discussion forum. >>>> >>>> >>> >>> --001a1144aa2ed362ce0541d092b5 >>> >>> >>> >> ************************************************************ >> ******************* >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >> >> >> ************************************************************ >> ******************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --001a114c9cccccca380541d62a18 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Thank you. > El 21/11/2016, a las 13:17, Art Kagel <art.kagel@gmail.com> escribió: > > onstat -k describes the locks and what session is holding them (match the> owner column in onstat -k to the address column in onstat -u to find the > session id holding the locks. > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Mon, Nov 21, 2016 at 4:13 PM, Jorge Valenzuela <jorgervt@gmail.com> > wrote: > >> Thanks, onstat -u tell me how many locks does any session had, but which >> command tell me which table/record are the session locked ? >> >>> El 21/11/2016, a las 06:56, Lester Knutsen <lester@advancedatatools.com> >> escribió: >>> >>> Dead locks are caused by an application problem. Did you release new >> code? >> Or, >>> did you business change such that many users are trying to access the >> same >>> records? On the database side, the main thing to do is make sure all your >>> tables are created to use row level locking mode. Monitor and find the >>> sessions that cause the deadlocks and then exam the application logic. >>> >>> Regards - Lester >>> >>>> On 11/21/16 9:27 AM, Jorge Valenzuela wrote: >>>> Hi, >>>> Since about 3 weeks a go, there had been an increment of dead locks in >> the >>>> database server and the users say the system is more slow than before. >> What >>>> can I do to reduce the dead locks and increase the system response ? >>>> Thanks in advance. >>>> --Apple-Mail-112EA2CF-C317-4221-9E31-9D8F13E86339 >>>> >>>> >>>> >>> >> ************************************************************ >> ******************* >>>> Forum Note: Use "Reply" to post a response in the discussion forum. >>>> >>> >>> -- >>> ______________________________________________________________________ >>> Lester Knutsen lester@advancedatatools.com >>> Advanced DataTools Corporation Voice: 703-256-0267 x102 >>> Visit our Web page: http://www.advancedatatools.com >>> ______________________________________________________________________ >>> >>> >>> >> ************************************************************ >> ******************* >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >> >> >> ************************************************************ >> ******************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --001a113f7776b684d10541d62f7c > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Thanks, I will use ixlocks. > El 21/11/2016, a las 13:22, Fernando Nunes <domusonline@gmail.com> escribió: > > You may find my "ixlocks" script useful: > > https://github.com/domusonline/InformixScripts/tree/master/scripts/ix > > In case of any issue with it please report. > > On Mon, Nov 21, 2016 at 10:13 PM, Jorge Valenzuela <jorgervt@gmail.com> > wrote: > >> Thanks, onstat -u tell me how many locks does any session had, but which >> command tell me which table/record are the session locked ? >> >>> El 21/11/2016, a las 06:56, Lester Knutsen <lester@advancedatatools.com> >> escribió: >>> >>> Dead locks are caused by an application problem. Did you release new >> code? >> Or, >>> did you business change such that many users are trying to access the >> same >>> records? On the database side, the main thing to do is make sure all your >>> tables are created to use row level locking mode. Monitor and find the >>> sessions that cause the deadlocks and then exam the application logic. >>> >>> Regards - Lester >>> >>>> On 11/21/16 9:27 AM, Jorge Valenzuela wrote: >>>> Hi, >>>> Since about 3 weeks a go, there had been an increment of dead locks in >> the >>>> database server and the users say the system is more slow than before. >> What >>>> can I do to reduce the dead locks and increase the system response ? >>>> Thanks in advance. >>>> --Apple-Mail-112EA2CF-C317-4221-9E31-9D8F13E86339 >>>> >>>> >>>> >>> >> ************************************************************ >> ******************* >>>> Forum Note: Use "Reply" to post a response in the discussion forum. >>>> >>> >>> -- >>> ______________________________________________________________________ >>> Lester Knutsen lester@advancedatatools.com >>> Advanced DataTools Corporation Voice: 703-256-0267 x102 >>> Visit our Web page: http://www.advancedatatools.com >>> ______________________________________________________________________ >>> >>> >>> >> ************************************************************ >> ******************* >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >> >> >> ************************************************************ >> ******************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a114396e44741b10541d6402d > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

As others have answered, modern systems won't function well with page locking specially if your system is AIX or Windows (these use 4KB pages by default) or if you configured dbspaces with larger pages, given the fact that the bigger the page, the more records it will hold, so higher is the probability of a single lock inhibit access to more rows. Row level locking may consume much more memory, but that's what everybody uses so unless you're running your database on a "micro-device" you should be ok :) The "problem" in changing it at the $ONCONFIG level is that it will change *only* the tables you create after.... The $ONCONFIG value is just a default to use in CREATE TABLE statements if the statement doesn't include the clause do define it. So, you may need to change your existing tables: UNLOAD TO 'tmp_file.sql' DELIMITER 'INSERT A TAB HERE'SELECT 'ALTER TABLE ' || TRIM(tabname) || ' LOCK MODE (ROW);' FROM systables WHERE tabid > 99 AND tabtype = 'T' AND locklevel = 'P' and then run the resulting SQL against the database. Use tab character as delimiter to get a clean/runable sql file Regards On Mon, Nov 21, 2016 at 10:10 PM, Jorge Valenzuela <jorgervt@gmail.com> wrote: > Thanks for your help. > By default page level locking is used by Informix and I never change this, > will it help if I change to row level locking or there are another > disventages > to do this ? > > > El 21/11/2016, a las 06:36, Art Kagel <art.kagel@gmail.com> escribió: > > > > The only way to eliminate deadlocks is to be disciplines when developing > > your applications. Always access table A before table B in a transaction. > > Deadlocks happen when two users need access to two or more of the same > > resources and lock then in different orders so that each user acquires a > > lock on one of these resources thereby locking the other out. > > > > Fix your applications. > > > > Art > > > > Art S. Kagel, President and Principal Consultant > > ASK Database Management > > www.askdbmgt.com > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on the IIUG, nor any other organization with which I > am > > associated either explicitly, implicitly, or by inference. Neither do > > those opinions reflect those of other individuals affiliated with any > > entity with which I am affiliated nor those of the entities themselves. > > > > On Mon, Nov 21, 2016 at 9:27 AM, Jorge Valenzuela <jorgervt@gmail.com> > > wrote: > > > >> Hi, > >> Since about 3 weeks a go, there had been an increment of dead locks in > the > >> database server and the users say the system is more slow than before. > What > >> can I do to reduce the dead locks and increase the system response ? > >> Thanks in advance. > >> --Apple-Mail-112EA2CF-C317-4221-9E31-9D8F13E86339 > >> > >> > >> ************************************************************ > >> ******************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > > > > --001a1144aa2ed362ce0541d092b5 > > > > > > > ************************************************************ > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1147c69c4c94d40541d6820d

Or, using my dbscript utility: dbscript -d <mydatabase> -t '*' -c 'ALTER TABLE %s LOCK MODE (ROW);' | dbaccess -e <mydatabse> - Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 21, 2016 at 4:40 PM, Fernando Nunes <domusonline@gmail.com> wrote: > As others have answered, modern systems won't function well with page > locking specially if your system is AIX or Windows (these use 4KB pages by > default) or if you configured dbspaces with larger pages, given the fact > that the bigger the page, the more records it will hold, so higher is the > probability of a single lock inhibit access to more rows. > > Row level locking may consume much more memory, but that's what everybody > uses so unless you're running your database on a "micro-device" you should > be ok :) > > The "problem" in changing it at the $ONCONFIG level is that it will change > *only* the tables you create after.... The $ONCONFIG value is just a > default to use in CREATE TABLE statements if the statement doesn't include > the clause do define it. > > So, you may need to change your existing tables: > > UNLOAD TO 'tmp_file.sql' DELIMITER 'INSERT A TAB HERE'> SELECT 'ALTER TABLE ' || TRIM(tabname) || ' LOCK MODE (ROW);' > FROM systables > WHERE tabid > 99 AND tabtype = 'T' AND locklevel = 'P' > > and then run the resulting SQL against the database. Use tab character as > delimiter to get a clean/runable sql file > > Regards > > On Mon, Nov 21, 2016 at 10:10 PM, Jorge Valenzuela <jorgervt@gmail.com> > wrote: > > > Thanks for your help. > > By default page level locking is used by Informix and I never change > this, > > will it help if I change to row level locking or there are another > > disventages > > to do this ? > > > > > El 21/11/2016, a las 06:36, Art Kagel <art.kagel@gmail.com> escribió: > > > > > > The only way to eliminate deadlocks is to be disciplines when > developing > > > your applications. Always access table A before table B in a > transaction. > > > Deadlocks happen when two users need access to two or more of the same > > > resources and lock then in different orders so that each user acquires > a > > > lock on one of these resources thereby locking the other out. > > > > > > Fix your applications. > > > > > > Art > > > > > > Art S. Kagel, President and Principal Consultant > > > ASK Database Management > > > www.askdbmgt.com > > > > > > Blog: http://informix-myview.blogspot.com/ > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > opinions > > > and do not reflect on the IIUG, nor any other organization with which I > > am > > > associated either explicitly, implicitly, or by inference. Neither do > > > those opinions reflect those of other individuals affiliated with any > > > entity with which I am affiliated nor those of the entities themselves. > > > > > > On Mon, Nov 21, 2016 at 9:27 AM, Jorge Valenzuela <jorgervt@gmail.com> > > > wrote: > > > > > >> Hi, > > >> Since about 3 weeks a go, there had been an increment of dead locks in > > the > > >> database server and the users say the system is more slow than before. > > What > > >> can I do to reduce the dead locks and increase the system response ? > > >> Thanks in advance. > > >> --Apple-Mail-112EA2CF-C317-4221-9E31-9D8F13E86339 > > >> > > >> > > >> ************************************************************ > > >> ******************* > > >> Forum Note: Use "Reply" to post a response in the discussion forum. > > >> > > >> > > > > > > --001a1144aa2ed362ce0541d092b5 > > > > > > > > > > > ************************************************************ > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a1147c69c4c94d40541d6820d > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113712c26955780541d6b933

One thing to be aware of. The most common cause of deadlocks may be accessing table-1 and then table-2 in one transaction and then accessing the two tables in the opposite order in another. However it is possible to run into a deadlock by accessing a single table. This can happen when one transaction is accessing via an index and another is accessing using a sequential scan. You might want to check syssqexplain where sqx_seqscan > 0 to determine if any running statements are doing a sequential scan and then examine the query to see if it is going against a fairly active table which other threads are using an index to access the data. Also don't forget that an ANSI database uses repeatable read isolation, so the risk of a deadlock is much greater. If you are running into sequential scans, then you might want to see what you can do to avoid that. Another thing which could cause a deadlock is if some queries are using something like order by col1 and another is using order by col1 desc. Madison Pruet Retired and Loving it On Monday, November 21, 2016 3:35 PM, Jorge Valenzuela <jorgervt@gmail.com> wrote: Thank you, for all your help, I will talk with the developers and I will tests changing from page to row locking. > El 21/11/2016, a las 13:16, Art Kagel <art.kagel@gmail.com> escribió: > > Row locking is preferred for any OLTP environment, page locking for Data > Warehouse. There is slightly more overhead and the engine will assign more > memory to locks, but the reduction in lock contention normally more than > makes up for the added lock overhead. > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Mon, Nov 21, 2016 at 4:10 PM, Jorge Valenzuela <jorgervt@gmail.com> > wrote: > >> Thanks for your help. >> By default page level locking is used by Informix and I never change this, >> will it help if I change to row level locking or there are another >> disventages >> to do this ? >> >>> El 21/11/2016, a las 06:36, Art Kagel <art.kagel@gmail.com> escribió: >>> >>> The only way to eliminate deadlocks is to be disciplines when developing >>> your applications. Always access table A before table B in a transaction. >>> Deadlocks happen when two users need access to two or more of the same >>> resources and lock then in different orders so that each user acquires a >>> lock on one of these resources thereby locking the other out. >>> >>> Fix your applications. >>> >>> Art >>> >>> Art S. Kagel, President and Principal Consultant >>> ASK Database Management >>> www.askdbmgt.com >>> >>> Blog: http://informix-myview.blogspot.com/ >>> >>> Disclaimer: Please keep in mind that my own opinions are my own opinions >>> and do not reflect on the IIUG, nor any other organization with which I >> am >>> associated either explicitly, implicitly, or by inference. Neither do >>> those opinions reflect those of other individuals affiliated with any >>> entity with which I am affiliated nor those of the entities themselves. >>> >>> On Mon, Nov 21, 2016 at 9:27 AM, Jorge Valenzuela <jorgervt@gmail.com> >>> wrote: >>> >>>> Hi, >>>> Since about 3 weeks a go, there had been an increment of dead locks in >> the >>>> database server and the users say the system is more slow than before. >> What >>>> can I do to reduce the dead locks and increase the system response ? >>>> Thanks in advance. >>>> --Apple-Mail-112EA2CF-C317-4221-9E31-9D8F13E86339 >>>> >>>> >>>> ************************************************************ >>>> ******************* >>>> Forum Note: Use "Reply" to post a response in the discussion forum. >>>> >>>> >>> >>> --001a1144aa2ed362ce0541d092b5 >>> >>> >>> >> ************************************************************ >> ******************* >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >> >> >> ************************************************************ >> ******************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --001a114c9cccccca380541d62a18 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.