Is it a correct behavior
I have a test table which has only one column called name. I set this table to row lock and create an index on name. I have a jdbc program to operate this table. In the table ( I call it test), I have 8 records. I do "select name from test where name = 'pppp' for update". I found all 8 records locked. I use informix dynamic server 9.21. Is it a correct behavior, because I expect only one record will be locked. Is it because the index doesn't take effect? or I need to do addition things? Thanks in advance.
excuse mi english check your lock level may be its page level must be row level ----- Original Message ----- From: "JACK" <jackleeok@yahoo.com> To: <ids@iiug.org> Sent: Thursday, November 27, 2003 9:36 PM Subject: Is it a correct behavior [2266] > I have a test table which has only one column called name. I set this table to row lock and create an index on name. I have a jdbc program to operate this table. In the table ( I call it test), I have 8 records. I do "select name from test where name = 'pppp' for update". I found all 8 records locked. I use informix dynamic server 9.21. > > Is it a correct behavior, because I expect only one record will be locked. > Is it because the index doesn't take effect? or I need to do addition things? > > Thanks in advance. >
Yes, that is correct behavior. OK maybe not correct but expected. If you set your explain on, you will notice that the table is scanned. It will not use the index because all 8 rows fit in a data page and it is obviously easier to just pull that 1 data page and seach the 8 rows for your criteria than to read the 1 index page which would point to the same data page anyway. A good rule of thumb is that Informix will scan almost any table with less than 100 rows in it. The locking of the records is dependant on the method it used to run the select. If you want it to explicitly lock just the one index row and the one data row, use an optimizer directive. Problem with optimizers, are that you don't always want the fastest method as in this case. Keeping your transactions short also resolves most of these locking issues. Scott M. Kolaya Lead Database Administrator Fleet Libris Information Solutions Ph: 518-471-1830 Fax: 518-471-1841 <mailto:Scott_M_Kolaya@fleet.com> -----Original Message----- From: JACK [mailto:jackleeok@yahoo.com] Sent: Thursday, November 27, 2003 10:36 PM To: ids@iiug.org Subject: Is it a correct behavior [2266] I have a test table which has only one column called name. I set this table to row lock and create an index on name. I have a jdbc program to operate this table. In the table ( I call it test), I have 8 records. I do "select name from test where name = 'pppp' for update". I found all 8 records locked. I use informix dynamic server 9.21. Is it a correct behavior, because I expect only one record will be locked. Is it because the index doesn't take effect? or I need to do addition things? Thanks in advance.
I'd be inclined to believe that it was because the index is not being used and data rows are being considered. What is the output from SET EXPLAIN? -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche "Necrophilia means never having to say ... well, anything!" - Captain Pedantic "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco >From: "JACK" <jackleeok@yahoo.com> >To: ids@iiug.org >Subject: Is it a correct behavior [2266] Date: Thu, 27 Nov 2003 22:36:02 >-0500 (EST) >Received: from ace.iiug.org ([216.177.38.212]) by mc9-f4.hotmail.com with >Microsoft SMTPSVC(5.0.2195.6713); Thu, 27 Nov 2003 20:45:08 -0800 >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org >(8.12.10-14/8.12.8) with ESMTP id hAS3f14S011445;Thu, 27 Nov 2003 23:30:47 >-0500 (EST) >Received: (from nobody@localhost)by ace.iiug.org (8.12.10-14/8.12.8/Submit) >id hAS3a2Db011334;Thu, 27 Nov 2003 22:36:02 -0500 (EST) >X-Message-Info: UZmYcfFpTCcG/p8DEmFrbuR21tsLR6AR >Message-Id: <200311280336.hAS3a2Db011334@ace.iiug.org> >Apparently-To: forum.subscriber@iiug.org >Sender: forum.subscriber@iiug.org >Precedence: bulk >Return-Path: nobody@ace.iiug.org >X-OriginalArrivalTime: 28 Nov 2003 04:45:08.0921 (UTC) >FILETIME=[669CAA90:01C3B56A] > >I have a test table which has only one column called name. I set this table >to row lock and create an index on name. I have a jdbc program to operate >this table. In the table ( I call it test), I have 8 records. I do "select >name from test where name = 'pppp' for update". I found all 8 records >locked. I use informix dynamic server 9.21. > >Is it a correct behavior, because I expect only one record will be locked. >Is it because the index doesn't take effect? or I need to do addition >things? > >Thanks in advance. > _________________________________________________________________ Sign-up for a FREE BT Broadband connection today! http://www.msn.co.uk/specials/btbroadband
Have you updated statistics on the table after creating the index. Sometimes the index is not used until updated stats is executed. Ronald Twaddell (ronald.twaddell@covance.com) SR. DBA Covance -----Original Message----- From: JACK [mailto:jackleeok@yahoo.com] Sent: Thursday, November 27, 2003 10:36 PM To: ids@iiug.org Subject: Is it a correct behavior [2266] I have a test table which has only one column called name. I set this table to row lock and create an index on name. I have a jdbc program to operate this table. In the table ( I call it test), I have 8 records. I do "select name from test where name = 'pppp' for update". I found all 8 records locked. I use informix dynamic server 9.21. Is it a correct behavior, because I expect only one record will be locked. Is it because the index doesn't take effect? or I need to do addition things? Thanks in advance. ----------------------------------------------------- Confidentiality Notice: This e-mail transmission may contain confidential or legally privileged information that is intended only for the individual or entity named in the e-mail address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or reliance upon the contents of this e-mail is strictly prohibited. If you have received this e-mail transmission in error, please reply to the sender, so that we can arrange for proper delivery, and then please delete the message from your inbox. Thank you.