Oninit cdi / Archive
Talk to an expert →

TABLE LOCK - IN EXCLUSIVE MODE

Posted in 2005

Topics: Stored Procedures & SPL, Transactions, Locking & Isolation

Tah Davis — — source: IIUG Forums & Mailing Lists

<html><div style='background-color:'><DIV class=RTE>Hi there, </DIV>There is a program that use LOCK TABLE table_name IN EXCLUSIVE MODE.&nbsp;&nbsp;Here is partial of code in the program; <DIV></DIV> <DIV></DIV>&nbsp;&nbsp; SELECT field_1, field_2, field_3, ...... <DIV></DIV>&nbsp;&nbsp; FROM table_name <DIV></DIV>&nbsp;&nbsp; WHERE field_1 IS NULL AND&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <DIV></DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; field_2 IS NULL AND&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <DIV></DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; field_3 IS NULL AND&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <DIV></DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; field_4 IS NULL&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp; <DIV></DIV> <P>&nbsp;&nbsp;INTO TEMP <STRONG>tmp_table_name </STRONG>WITH NO LOG&nbsp;&nbsp;&nbsp;&nbsp;</P> <P>&nbsp;&nbsp;&nbsp;</P> <DIV></DIV> <DIV></DIV>&nbsp;&nbsp;&nbsp;&nbsp; ....................&nbsp;&nbsp;then later in the code .......................... <DIV></DIV> <DIV></DIV>BEGIN WORK&nbsp;&nbsp; {* 7550 *}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <DIV></DIV><STRONG>LOCK TABLE tmp_table_name IN EXCLUSIVE MODE&nbsp;&nbsp; </STRONG> <DIV></DIV>WHENEVER ERROR CONTINUE&nbsp;&nbsp; <DIV></DIV>&nbsp;&nbsp; <DIV></DIV>&nbsp;&nbsp; ................................................................................ <DIV></DIV> <DIV></DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LET rec_cnt = rec_cnt + 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <DIV></DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF NOT rec_cnt MOD 100 THEN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp; <DIV></DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DISPLAY rec_cnt, " Processed" AT 5,5&nbsp;&nbsp;&nbsp;&nbsp; <DIV></DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COMMIT WORK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; <DIV></DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BEGIN WORK&nbsp;&nbsp; {* 7550 *}&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <DIV></DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <STRONG>LOCK TABLE tmp_table_name IN EXCLUSIVE MODE </STRONG> <DIV></DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;END IF&nbsp;&nbsp;&nbsp;&nbsp; <DIV></DIV> <DIV></DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <DIV></DIV> <P>&nbsp;&nbsp;&nbsp;&nbsp; ................................................................................ ...... </P> <P>This program is running sometimes a huge batch of records (200,000+ rows).&nbsp; Yes, there is nite cron job also but eventually it needs to run during a day also.</P> <DIV></DIV> <DIV></DIV> <DIV></DIV>My question is <STRONG><U>how do avoid the LOCK - IN EXCLUSIVE MODE? </U></STRONG> <DIV></DIV> <P>Because every time when someone is running this program, another CANNOT run this program or any other programs that access the same table at all.&nbsp;&nbsp;It issued some source of error that indicated TABLE LOCK ( such as -144 ).&nbsp;&nbsp;Is there a better method or procedure that handle multi-users running LOCK TABLE applications?</P> <P>T Davis</P> <DIV></DIV> <DIV></DIV> <DIV></DIV> <DIV></DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div></html>

↪ replying to Tah Davis
Obnoxio The Clown — — source: IIUG Forums & Mailing Lists

Tah Davis said: > > <html><div style='background-color:'><DIV class=RTE></P> > <P>T Davis</P> > <DIV></DIV> > <DIV></DIV> > <DIV></DIV> > <DIV></DIV> </div></html> Once more, with feeling, please! -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche A smile is a gift that is free to the giver and precious to the recipient. But giving someone the finger is free too, and I find it more personal and sincere.

Related threads