Re: LOCKING problem

Cosmo Lee ("*NO-SPAM*cosmo"@echonyc.com) wrote: : insert into tab_2 : select * from tab_1; : I noticed that as soon as I run this, the number of locks shoot through : the roof. Yes, but what type of locks were they, S or X? If they were S, then they're on the selected table. If they're X, they're on the inserted table. : I thought that the problem was the isolation level causing : the rows in the selected table to be locked, so I used the following: : set isolation to dirty read; : This did not help. Okay, then they're on the inserted table. : So, I thought, OK, I'm wrong, how about locking the table - in fact I : lock _both_ tables. To my surprise, locking both tables has no effect. : I run my query and the locks still go through the roof and I max out in : minutes. : begin work; : lock table tab_1 in share mode; : lock table tab_2 in share mode; Your theory was sound, but in order to avoid X-locks on the rows, you have to X-lock the table. LOCK TABLE tab_2 IN EXCLUSIVE MODE; June ---- June Tong Informix Software ---- ---- Senior Consultant (650) 926-6140 ---- ---- International Support junet@informix.com ---- ---- Location-du-jour: Stockholm, Sweden ---- * * Standard disclaimers apply * - Please do not send me requests/questions by mail. When I have the knowledge - and time permits, I try to answer questions on comp.databases.informix, but - travel schedule, time, and volume make responding to personal requests - difficult and often slow. Please call your local Informix Technical Support - organization for assistance with technical issues.