title: The total number of locks exceeds the lock table size in MySQL
body: <p>I have a table which is defined like this: 
  <img src="https://i.stack.imgur.com/ZCu1i.png" alt="enter image description here">
  Where <code>ProviderId</code> is the primary key, and <code>StateID</code> is the foreign key, that comes from another table. It is running in my laptop, therefore, it is a local database. In my table I have around 9 million entries. I know for a fact, there are duplicate entries in the table, but they are duplicate only if we compare them with a few of the fields. In order to delete the duplicate entries I ran the query found below:</p>
  
  <pre><code>SET SQL_SAFE_UPDATES=0;
  DELETE p1.*
  FROM providers AS p1
  JOIN (SELECT ProviderName AS PName, Address AS PAddr, StateID, Zip, MIN(ProviderId) AS PId
        FROM providers
        GROUP BY PName, PAddr, StateID, Zip
        HAVING COUNT(*) &gt; 1) AS p2
  ON p1.ProviderName = p2.PName
  AND p1.Address = p2.PAddr
  AND p1.StateID = p2.StateID
  AND p1.Zip = p2.Zip
  AND p1.ProviderId &gt; p2.PId;
  </code></pre>
  
  <p>The point is that, it was running for about 4 and a half hours, and it showed me the error message written on the title. You can also see it : 
  <img src="https://i.stack.imgur.com/ukvWc.png" alt="enter image description here">
  How can I make my query faster, and overcome the error message that I got?</p>