Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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,

share|improve this question

1 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.