Re: LOCKING problem
Cosmo Lee wrote:
}
} Online DSA 7.22, Unixware 2.1., row-level locking on tables
}
} I'm trying to load all the data from one table into another using the
} following:
}
} 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. 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.
}
} 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;
}
} What's up with dat???
}
} Signed,
} Puzzled and Perturbed
}
} --
} * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
} ADDRESS ALTERED TO FOIL SPAMMERS: Remove "*NO-SPAM*" to reply.
}
} Cosmo Lee Multi-User Computer Systems Brooklyn, NY
}
} "JUST SAY 'NO' TO SPAM"
} * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Just lock the table you are inserting into in exclusive mode (If you
can....)
begin;
lock table tab_2 in exclusive mode;
insert into tab_2
select * from tab_1;commit;
Beware of the long transactions!!!