RE: Lock issue with replicated server

Paul, Thanks for that info, as for an other workaround, we successfully created the two indexes by encapsulating the statements in a begin work/commit work. Thanks again, John -----Original Message----- From: PaulITID.Matthews@chase.com [mailto:PaulITID.Matthews@chase.com] Sent: Friday, May 14, 1999 4:46 AM To: Porcello, John Cc: informix-list@iiug.org Subject: Re: Lock issue with replicated server John, This seems to be a known bug/feature of Data Replication in that the secondary server puts a lock on the table on the primary when the secondary has to create an index associated with that table. The way round this problem is to run on a per session basis the SET LOCK MODE TO WAIT nn command in order to allow the primary to wait for the secondary and possibly to also increase the DEADLOCK_TIMEOUT parameter in the onconfig file. This is documented in the Admin guide in the Data Replication setion, in fact We have experienced this ourselves and has proven to be a huge problem in that vendor supplied 4GL has had to be recompiled to add the set lock mode statement every time a session creates an index. The implications of this were such that we considered dropping DR and would recomend anyone considering setting up Dat Replication to understand this issue fully if they support an environment that creates indexes frequently. Why there can't be a system wide option done at config file level I don't know and brings to mind the similar problem we encountered where roles can only be granted on a session basis only Sometimes I wonder how well Informix think these things through from a customer perspective Rgds Paul "Porcello, John" <jep-corp@kaman.com> on 13/05/99 18:51:42 To: "Informix (E-mail)" <informix-list@iiug.org> cc: "Frati, Louis" <lrf-corp@kaman.com> (bcc: Paul'ITID' Matthews/CHASE) Subject: Lock issue with replicated server We are experiencing a strange issue regarding creating indexes on Informix 7.30 with replication enabled. We have three similar instances, all with a copy of the same database. Our production database, where we are experiencing the issue, is the only replicated database. I have noticed that Userthread address 20479caa0 (Session ID 16 in the below example), which is a Informix daemon thread that I do not see on my other servers, has a table lock on 4003da (feb98bs_a). I have come to the assumption that this is a replication issue. Can anyone explain what is going on with this? Any information would be appreciated. The following is an example of our problem: create index x1feb98bs on feb98bs_a (seg01,rl_code); create index x2feb98bs on feb98bs_a (seg02,rl_code);# ^ # 242: Could not open database table (jburns.feb98bs_a). # 113: ISAM error: the file is locked. # onstat -uk Informix Dynamic Server Version 7.30.FC4 -- On-Line (Prim) -- Up 08:25:20 -- 293160 Kbytes Userthreads address flags sessid user tty wait tout locks nreads nwrites 20479caa0 ---P--D 16 root - 0 0 1 0 6 2047adf90 L--PR-- 1478 root ttyp4 2001d9fc0 30 1 0 0 60 active, 128 total, 71 maximum concurrent Locks address wtlist owner lklist type tblsnum rowid key#/bsiz 2001d9fc0 2047adf90 20479caa0 0 HDR+S 4003da 0 0 2001dab70 0 2047adf90 0 S 100002 203 0 46 active, 200000 total, 65536 hash buckets onstat -g ses 1478 Informix Dynamic Server Version 7.30.FC4 -- On-Line (Prim) -- Up 08:25:49 -- 293160 Kbytes session #RSAM total used id user tty pid hostname threads memory memory 1478 root ttyp4 19812 kmc9 1 73728 48208 tid name rstcb flags curstk status 3431 sqlexec 2047adf90 L--PR-- 2456 2047adf90sleeping(secs: 16) Memory pools count 1 name class addr totalsize freesize #allocfrag #freefrag 1478 V 20646a028 73728 25520 293 19 name free used name free used overhead 0 176 scb 0 144 opentable 0 4536 filetable 0 1632 ru 0 280 log 0 2168 temprec 0 1696 keys 0 376 ralloc 0 4768 gentcb 0 13392 ostcb 0 2896 sqscb 0 10064 rdahead 0 416 hashfiletab 0 552 osenv 0 2656 sqtcb 0 1984 fragman 0 472 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers 1478 CREATE INDEX kmc_prod CR Wait 30 0 0 7.30 Current SQL statement : create index i1feb98bs_a on feb98bs_a (rl_code) Last parsed SQL statement : create index i1feb98bs_a on feb98bs_a (rl_code) TIA, John E. Porcello, Jr. Corporate DBA Kaman Corporation jep-corp@kaman.com My views and opinions may not reflect those of Kaman Corporation.