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.

I'm adding a foreign key constraint to table a (17mil rows) that references table b (15mil rows):

>ALTER TABLE a ADD FOREIGN KEY (b_id) REFERENCES b (id);
ERROR 1206: The total number of locks exceeds the lock table size

I guess the client is acquiring a lock for each row, so I tried locking the table first without success:

>LOCK TABLES a WRITE;
Query OK, 0 rows affected (0.00 sec)

>ALTER TABLE a ADD FOREIGN KEY (b_id) REFERENCES b (id);
ERROR 1206: The total number of locks exceeds the lock table size
  1. Why exactly are there so many locks, i.e. what is being locked in this query?
  2. Why didn't locking the table solve the problem? Do I need to do the lock in a different way or lock something else as well?
  3. If the only solution is to increase the innodb_buffer_pool_size, how large does it need to be for this query?

I obviously don't need a sustainable solution - just need to do this the one time. For example, the box isn't a dedicated database server but I could increase the pools size brief it for this query and then set it back to something reasonable afterwards.

share|improve this question

migrated from stackoverflow.com Feb 29 '12 at 18:54

This question came from our site for professional and enthusiast programmers.

    
Did you try locking table B as well? –  antlersoft Feb 28 '12 at 15:47
    
I'm trying that now; it takes some time before the error occurs. –  jmilloy Feb 28 '12 at 15:49
    
No it didn't work. –  jmilloy Feb 28 '12 at 16:11
    
A 1GB pool size was sufficient, but my questions all still stand. –  jmilloy Feb 28 '12 at 18:58
    
About your LOCK TABLES question and InnoDB. From Locks Set by Different SQL Statements in InnoDB (second to last paragraph): LOCK TABLES sets table locks, but it is the higher MySQL layer above the InnoDB layer that sets these locks. –  ypercube Mar 30 '12 at 22:26

1 Answer 1

According to the MySQL Performance Blog

Lets start with a bit of background – in Innodb row level locks are implemented by having special lock table, located in the buffer pool where small record allocated for each hash and for each row locked on that page bit can be set. This in theory can give overhead as low as few bits per row, so lets see how it looks in practice

The same reference brings this out...

So we locked over 100K rows using about 44KB. This is still quite efficient using less than 4 bits per locked row.

In practice this means memory consumption by row level locks should not be the problem even for rather large databases – even billion of locked rows should take half GB of memory, which is small fraction of memory used on serious systems. Furtermore you would unlikely need or want to lock every row in your table/database which makes it even smaller problem.

Evidently, all 17 million rows need to be locked. Based on the link from mysqlperformanceblog.com:

  • 100,000 rows takes 44 KB
  • 17,000,000 rows should take 7480 KB, just over 7 MB

I can easily see an unconfigured innodb_buffer_pool_size (Default is 128M in MySQL 5.5, and 8M before MySQL 5.5) causing a problem.

OK Let's get back to your questions:

Why exactly are there so many locks, i.e. what is being locked in this query?

Every row in an InnoDB table must be locked. This makes the data available via MVCC, ACID Compliance, and Transaction Isolation.

Why didn't locking the table solve the problem? Do I need to do the lock in a different way or lock something else as well?

Same as Answer 1

If the only solution is to increase the innodb_buffer_pool_size, how large does it need to be for this query?

There must be enough RAM to not only hold your working set of InnoDB data and index pages, but also small amount of overhead (44KB per 100,000 rows) for table locks.

share|improve this answer
1  
It sounds like LOCK TABLES has no effect with innodb. Can you explain why we can't override row locking with a table lock? Or why adding a foreign key constraint would use row locking instead of table locking? What about table locking violates ACID? Isn't a foreign key constraint atomic and isolated at the table level, not the row level? –  jmilloy Feb 29 '12 at 22:51

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.