How does HPL handle lock?
Posted in 2011
Topics: Performance & Tuning, Error Codes & Troubleshooting, Triggers, Constraints & Referential Integrity
I'm working on a multi-threaded process to load the data files to a bunch of
tables. According to the data files' sizes, some files are loaded through
"load" sql scripts, others are loaded through high performance loader scripts.
The process will load the tables in an order to satisfy the foreign key
constraints.
Somehow, when two tables all reference to a same key, and they are loaded at
the same time, one of the table's load thread will throw error below:
691: Missing key in referenced table for referential constraint (xxx).
107: ISAM error: record is locked.
I added "set lock mode to wait 2" to the sql scripts, and the process works
fine when all the data files are loaded through sql scripts.
Though HPL jobs are running one by one, a HPL job could be run at the same
time when a sql script is running. I could not figure out a way to add "lock
mode" statement to HPL load job description.
My question is how HPL handles the load job if the job encounters a locked
reference key? Will it wait until the locked record is free? Is there any way
to set lock mode in a HPL load job?
Thanks in advance for your response!
Summer