MySQL hang in load data infile command

Question

From the link: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?

I had changed the command order (unlock before commit + replace some queries to slave), and the db server runs pretty well . But I want to ask more question around why my db server hangs?

I'm not sure of the main reason that @RolandoMySQLDBA mention that lets the db server hang. You can test my command: Session1: (lock table and don't throw unlock tables command).

#session 1
Lock table **v3_zone_date** WRITE;
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date 
 FIELDS TERMINATED BY ','; 
COMMIT;
SET AUTOCOMMIT=1;#ok
#Session2: 
SELECT * FROM **v3_cam_date** LIMIT 1; #ok
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date 
 FIELDS TERMINATED BY ','; 
COMMIT; 
SET AUTOCOMMIT=1;#ok

So, if I tried to get x lock from table v3_domain_date in session1, and then running query to other tables in session 2, everything Ok (not lock or hang out db). Of course, I can't query to the v3_domain_date (be cause the table was locked with x lock).

can any one explain for me? Thanks,

Answer 1

Please look carfully at session 1.

Lock table v3_zone_date WRITE; 
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date 
FIELDS TERMINATED BY ','; 
COMMIT; 
SET AUTOCOMMIT=1;

should be

Lock table v3_zone_date WRITE; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date 
FIELDS TERMINATED BY ','; 
UNLOCK TABLES;

No need to have these lines

SET AUTOCOMMIT=0;
COMMIT; 
SET AUTOCOMMIT=1;

because MySQL 5.0 Certification Study Guide, Chapter 29, Page 418 Bulletpoint #1 says

UNLOCK TABLES implicitly commits only if you have explicitly locked tables with LOCK TABLES. SET AUTOCOMMIT = 1 implicitly commits only if autocommit mode wasn't already enabled.

LOCK TABLES will hold up any transaction-based row locks that are trying to be acquired.

Since the tables are locked independently, you must just need to increase the size of your InnoDB Buffer Pool (innodb_buffer_pool_size) to make more room for row locks across all InnoDB tables.