Informix Locks On Select count(*)

hi, i have a question, does informix lock table when doing a select count(*) ? Thanks Horacio

btw, i have a where clause in my select statement. will those records be locked? thanks

It takes a momentary lock on the table's partition header page if there is no filter or DISTINCT clause in the select count(*). If there is a filter that matches an index key then locks will be taken on the index nodes and leaves. If there is a DISTINCT/UNIQUE clause or if the filter does not match an index then data pages will be momentarily locked while the rows on each page are examined. These locks are all transient - short lived. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Sun, Jan 29, 2012 at 11:06 AM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > hi, > > i have a question, does informix lock table when doing a select count(*) ? > > Thanks > Horacio > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec5186cbe25b12004b7ae887b

hi, even if it is in committed_read mode? as a follow up question, So what's the difference between a select * and a select count(*) ? is this correct? Committed Read select * will not place shared locks select count(*) will place shared locks Thanks Carlo

hi, even if it is in committed_read mode? as a follow up question, So what's the difference between a select * and a select count(*) ? is this correct? Committed Read select * will not place shared locks select count(*) will place shared locks Dirty Read select * will not place shared locks select count(*) will not place shared locks. Thanks Carlo

Hello, Art already explained it, but we can go a bit deeper... Short answer to your question would be "no", but that would hide how things work. Informix keeps the number of records on a table (or partition) in the partition header. When you do a simple "SELECT COUNT(*) FROM table" with no WHERE clause it can retrieve the number from the partition header. That's why in Informix, no matter the size of the table a SELECT COUNT(*) is usually instantaneous. This is not so with other databases. If you ask other databases's DBAs they're all scared about SELECT COUNT(*)... But, as usual, all good things comes with a baggage.... If you're in COMMITTED READ it must tell you the COMMITTED count(*).... How can it do that if there are pending (not yet committed) INSERTs or DELETEs? Simply by waiting for all of them to commit or rollback and at the same time (here comes the worst part) preventing new INSERTs and DELETEs. That's why you may notice that the INSERTs get stuck for example. It's not that there are any real records lock (a SELECT * will continue to work), but the partition header is locked. If your applications do something slow after opening a transaction and making an INSERT this can become a real problem. Also note that using COMMITTED READ LAST COMMITTED will not avoid this. Solutions? Several: 1- Run the SELECT COUNT(*) in DIRTY READ. In tables with high INSERT/DELETE rates the count(*) is extremely volatile. And many times the count(*) is used just to see if there is any work to do, or if the number of records is above a certain threshold. And in most cases geting a 5 is more or less the same as getting a 10 or 20. Getting a 1000 is the same as getting 1020 etc. 2- Run the SELECT COUNT(*) with a dummy WHERE (WHERE 1 = 1). This will avoid the use of the partition header 3- Try to reduce the time between the INSERT and the COMMIT or ROLLBACK Regards. On Mon, Jan 30, 2012 at 4:31 AM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > hi, > > even if it is in committed_read mode? > as a follow up question, > > So what's the difference between a select * and a select count(*) ? > > is this correct? > > Committed Read > > select * will not place shared locks > select count(*) will place shared locks> > Dirty Read > > select * will not place shared locks > select count(*) will not place shared locks.> > Thanks > Carlo > > > > ******************************************************************************* > 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... --20cf303b42d3f84e2404b7bc4e37

select * will also place breif shared locks on each row as it is touched and the lock will be released when the query moves on to the next row. The difference for select count(*) is that if there is no filter the engine can just read the nrows field in the table's partition header to find out how many rows there are - so only a partition lock. If there is a filter on an indexed column the engine can navigate the index to count the matching rows - so only key locks. If the filter is on a non-indexed column then the engine has to read data pages just like a SELECT * requiring page or row locks depending on the lock mode of the table. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Sun, Jan 29, 2012 at 11:24 PM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > hi, > > even if it is in committed_read mode? > as a follow up question, > > So what's the difference between a select * and a select count(*) ? > > is this correct? > > Committed Read > > select * will not place shared locks > select count(*) will place shared locks> > Thanks > Carlo > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3ba68161d20304b7bccd52

Ok Thanks a lot for the really informative answer. Is it the same when select count(*) has a filter clause? So dirty read will forego any locks even on select count(*) Thanks

If it has a filter clause it will have to work as any other query. So the situations specific to the partition header will not apply. Dirty read will simply get the current nrows field of the partition header. It will not wait for nothing and will cause no one to wait. Naturally the drawback is that the value may reflect uncommitted data. Regards. On Mon, Jan 30, 2012 at 2:05 PM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > Ok > > Thanks a lot for the really informative answer. > Is it the same when select count(*) has a filter clause? > So dirty read will forego any locks even on select count(*) > > Thanks > > > > ******************************************************************************* > 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... --20cf300faebb90ff6f04b7bf8384

On Mon, Jan 30, 2012 at 03:03, Art Kagel <art.kagel@gmail.com> wrote: > select * will also place breif shared locks on each row as it is touched > and the lock will be released when the query moves on to the next row.> Watch out for REPEATABLE READ (aka SERIALIZABLE) isolation. Then the shared locks are held long enough to meet the repeatability requirements (end of transaction, which might be a statement or a formal transaction with BEGIN/COMMIT). > The difference for select count(*) is that if there is no filter the engine > can just read the nrows field in the table's partition header to find out > how many rows there are - so only a partition lock. If there is a filter > on an indexed column the engine can navigate the index to count the > matching rows - so only key locks. If the filter is on a non-indexed > column then the engine has to read data pages just like a SELECT * > requiring page or row locks depending on the lock mode of the table. > > [...sig snip...] > > On Sun, Jan 29, 2012 at 11:24 PM, NATYURAL HORACIO < > horacio.natyural@gmail.com> wrote: > > > > even if it is in committed_read mode? > > as a follow up question, > > > > So what's the difference between a select * and a select count(*) ? > > > > is this correct? > > > > Committed Read > > > > select * will not place shared locks > > select count(*) will place shared locks> -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --e89a8f6432c2b5e4ce04b7c056d0

hi there, my transaction isolation level is commmited read. art mentioned that when i do a select on a column with an index it will do an index key lock. here is my scenario i have this particular column in which the index used is date. however, i do not specify any time values. my select and select count statement reads on this and the value is quite large. hundreds of thousands. i have other filters in my where criteria. now there is an index on date. date rows can have hundreds of thousands of entries which are similar. select * from tbl_sample where date = ? and other_criteria = ? question is: while my select or select count is scanning the index for my filters, will it place shared locks? will my inserts have to wait for the select count to finish? Thanks

Also, Will setting isolation to dirty read help with this? Thanks

Yes. Can't hurt the concurrency issue. Just be aware that the counts may be slightly off since you may be counting uncommitted rows or not counting deleted rows that will rollback later. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Feb 1, 2012 at 8:24 AM, NATYURAL HORACIO <horacio.natyural@gmail.com > wrote: > Also, > > Will setting isolation to dirty read help with this? > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3ba42921cf8204b7e7872b

hi, so during the point when my select is happening my index is possibly locked. (date index). therefore, preventing inserts? you mentioned before about index key locking thanks

My scenario is this. I have lots of inserts thst are waiting to insert. They all seem to be locked. When i look at the onstat result, i see s lot of sql doing select count and select only against a particular table. Take note that there are no deletes and updates on that table.. Im guessing that my locks must indeed be coming from select or select count. Ive also noticed that some of those statements could last a minute. My isolation is committed read as of the moment. Will this be solved by last read committed? Is this informix's form of mvcc

Index pages that are being examined for a COUNT(*) with filter are exactly like any other SELECT query, the page that is being examined must be lock briefly to insure that it is not modified while it is being read. These locks are transitory and last only fractions of a second. Setting LOCK MODE TO wait will virtually eliminate any lockout or lock timeout errors in a session cause by some other session doing a COUNT() operation. I've been developing Informix applications for nearly 30 years and this is no issue at all in real operations. Honestly. The only time that concurrency is ever a real concern in Informix is when someone writes a poorly designed application that some something like: 1. BEGIN WORK; 2. SELECT ... FOR UPDATE; 3. Display the record for a user to review and modify; 4. Validate input; 5. Update the record if the user made changes; 6. COMMIT WORK; In an application like this one, if the user goes out to lunch or goes home or takes a long phone call before pressing the <SAVE> button then you will get other users experiencing lockout and lock timeout errors. If proper optimistic locking is followed, then there are never any concurrency problems using Informix. In the last few years new features built into 11.50 and 11.70 make optimistic locking easier to implement and faster to process, but lockouts have never been an issue. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Feb 1, 2012 at 9:18 AM, NATYURAL HORACIO <horacio.natyural@gmail.com > wrote: > hi, > > so during the point when my select is happening my index is possibly > locked. > (date index). > therefore, preventing inserts? you mentioned before about index key locking > > thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e8bacd00e7604b7ea5cf6

I think you need to look more closely at all of these applications. The problem may indeed be in the SELECT COUNT(*) queries but they might be in the insert transactions themselves with multiple users locking each other out due to inefficient application design. COMMITTED READ ... LAST COMMITTED isolation option will reduce the locking somewhat and so improve concurrency, yes. It is not a panacea and if the contention is between inserters then COMMITTED READ ... LAST COMMITTED will not help much because at insert time all users must still acquire a lock on the row or its page and on the indexes that are being updated by the insert. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Feb 1, 2012 at 9:31 AM, NATYURAL HORACIO <horacio.natyural@gmail.com > wrote: > My scenario is this. I have lots of inserts thst are waiting to insert. > They all seem to be locked. > When i look at the onstat result, i see s lot of sql doing select count and > select only against a particular table. Take note that there are no deletes > and updates on that table.. > Im guessing that my locks must indeed be coming from select or select > count. > Ive also noticed that some of those statements could last a minute. > My isolation is committed read as of the moment. > Will this be solved by last read committed? Is this informix's form of mvcc > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340f9de61e2b04b7ea7db6

Another typical situation is, Activate dbaccess tool, 1. BEGIN WORK; 2. SELECT ... FOR UPDATE; 3. go home.... ........ Thanks, Frank On Wed, Feb 1, 2012 at 12:25 PM, Art Kagel <art.kagel@gmail.com> wrote: > Index pages that are being examined for a COUNT(*) with filter are exactly > like any other SELECT query, the page that is being examined must be lock > briefly to insure that it is not modified while it is being read. These > locks are transitory and last only fractions of a second. Setting LOCK > MODE TO wait will virtually eliminate any lockout or lock timeout errors in > a session cause by some other session doing a COUNT() operation. > > I've been developing Informix applications for nearly 30 years and this is > no issue at all in real operations. Honestly. The only time that > concurrency is ever a real concern in Informix is when someone writes a > poorly designed application that some something like: > > 1. BEGIN WORK; > > 2. SELECT ... FOR UPDATE; > > 3. Display the record for a user to review and modify; > > 4. Validate input; > > 5. Update the record if the user made changes; > > 6. COMMIT WORK; > > In an application like this one, if the user goes out to lunch or goes home > or takes a long phone call before pressing the <SAVE> button then you will > get other users experiencing lockout and lock timeout errors. If proper > optimistic locking is followed, then there are never any concurrency > problems using Informix. In the last few years new features built into > 11.50 and 11.70 make optimistic locking easier to implement and faster to > process, but lockouts have never been an issue. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Feb 1, 2012 at 9:18 AM, NATYURAL HORACIO < > horacio.natyural@gmail.com > > wrote: > > > hi, > > > > so during the point when my select is happening my index is possibly > > locked. > > (date index). > > therefore, preventing inserts? you mentioned before about index key > locking > > > > thanks > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --90e6ba6e8bacd00e7604b7ea5cf6 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f2351b534d51704b7ea8a42

the insert statements are the last thing that the application needs to do. the table i'm referring to is using page mode locking. does it matter? are they being locked out due to this? there are no select for updates used. there are even no updates or deletes on that table. all that is done on the table is insert, select, and select count

the select count(*) query takes a long time though. it's doing a range scan against a hundreds of thousands of records. what i would actually like to know is if during the that time, the index will is locked thus preventing inserts. i just don't see any reason why inserts should be taking a long time. the index on date is very similar. it's jsut the day with no time. 01/01/01 for example.

Can you do the follow test and verification, 1) clone that table, 2) just do select count(*) on the cloned table( no inserting), what happens? 3) Just do insert on the cloned table( no selecting), what happens? Thanks, Frank On Wed, Feb 1, 2012 at 1:16 PM, NATYURAL HORACIO <horacio.natyural@gmail.com > wrote: > the select count(*) query takes a long time though. it's doing a range scan > against a hundreds of thousands of records. what i would actually like to > know > is if during the that time, the index will is locked thus preventing > inserts. > i just don't see any reason why inserts should be taking a long time. > > the index on date is very similar. it's jsut the day with no time. 01/01/01 > for example. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93404ab6b669704b7eb95e5

Page level locking will dramatically increase contention versus row level locking, yes. For an OLTP style system row level locking is preferred. When you say "the last thing that the application needs to do" are you implying that there are many other operations that are part of the same transaction? Are there several copies of this application running? Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Feb 1, 2012 at 1:11 PM, NATYURAL HORACIO <horacio.natyural@gmail.com > wrote: > the insert statements are the last thing that the application needs to do. > the table i'm referring to is using page mode locking. > does it matter? are they being locked out due to this? > there are no select for updates used. > there are even no updates or deletes on that table. > > all that is done on the table is insert, select, and select count > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e8bacc335f204b7ec2b3f

No the index node locks that the count is setting are transitory. They are only in place while a particular page is being read. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Feb 1, 2012 at 1:16 PM, NATYURAL HORACIO <horacio.natyural@gmail.com > wrote: > the select count(*) query takes a long time though. it's doing a range scan > against a hundreds of thousands of records. what i would actually like to > know > is if during the that time, the index will is locked thus preventing > inserts. > i just don't see any reason why inserts should be taking a long time. > > the index on date is very similar. it's jsut the day with no time. 01/01/01 > for example. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340f9dfcc43b04b7ec3361

hi, is this what you're saying? while a particular row or page is being examined, locks are placed on that row or page. it should only take a fraction of a second. but in the event of a slowdown, the examination may take longer. if we are to move to row mode locking, then there will be less contention. of course there are adjustments that we made. when you do a select statement, the lock is placed only on a particular row while it is being examined. when you do a select statement, and the lock is page lock, the lock is placed on the entire page while it is being examined. if we were in repeatable read, then the entire row or page is locked while the select is running and for the whole transaction. if we were in committed read, then the row or page is only locked while it is being examined. what about in dirty read then? does it also place shared locks while the row or page is being examined?

Yes to all. Dirty read places no locks and ignores any locks it encounters. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Fri, Feb 3, 2012 at 12:08 AM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > hi, > > is this what you're saying? > > while a particular row or page is being examined, locks are placed on that > row > or page. > it should only take a fraction of a second. but in the event of a slowdown, > the examination may take longer. > if we are to move to row mode locking, then there will be less contention. > of > course there are adjustments that we made. > > when you do a select statement, the lock is placed only on a particular row > while it is being examined. > when you do a select statement, and the lock is page lock, the lock is > placed > on the entire page while it is being examined. > > if we were in repeatable read, then the entire row or page is locked while > the > select is running and for the whole transaction. > if we were in committed read, then the row or page is only locked while it > is > being examined. > > what about in dirty read then? does it also place shared locks while the > row > or page is being examined? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9399c1f1daac104b80d02a5

really helpful! thanks a lot.

Hi, Does informix briefly lock a row when it scans over it even though it isnt part of the actual result set? For example Select * from tbl_example where a = 35 Would it briefly lock say a=34 while it is scanning over it even if it isnt part of the result set? Isolation is committed read. Thanks

Yes, or the entire page the row resides on if the table's lock level is page. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Fri, Feb 10, 2012 at 12:04 PM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > Hi, > > Does informix briefly lock a row when it scans over it even though it isnt > part of the actual result set? > For example > > Select * from tbl_example where a = 35> > Would it briefly lock say a=34 while it is scanning over it even if it isnt > part of the result set? > Isolation is committed read. > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3ba681bcb20f04b89f88bc