Databases
StackOverflow2013
Postsdbo
POHow to prevent MySQL InnoDB setting a lock for delete statement through JDBC
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to prevent MySQL InnoDB setting a lock for delete statement through JDBC
    text
    copied!<p>I have a multi-threaded client/server system with thousands of clients continuously sending data to the server that is stored in a specific table. This data is only important for a few days, so it's deleted afterwards.</p> <p>The server is written in J2SE, database is MySQL and my table uses InnoDB engine. It contains some millions of entries (and is indexed properly for the usage).</p> <p>One scheduled thread is running <em>once a day</em> to delete old entries. This thread could take a large amount of time for deleting, because the number of rows to delete could be very large (some millions of rows). On my specific system deletion of 2.5 million rows would take about 3 minutes.</p> <p>The inserting threads (and reading threads) get a timeout error telling me </p> <blockquote> <p>Lock wait timeout exceeded; try restarting transaction</p> </blockquote> <ol> <li>How can I simply get that state from my Java code? I would prefer handling the situation on my own instead of waiting. But the more important point is, how to prevent that situation?</li> <li><p>Could I use </p> <pre><code>conn.setIsolationLevel( Connection.TRANSACTION_READ_UNCOMMITTED ) </code></pre> <p>for the reading threads, so they will get their information regardless if it is most currently accurate (which is absolutely OK for this usecase)?</p></li> <li>What can I do to my inserting threads to prevent blocking? They purely insert data into the table (primary key is the tuple <em>userid</em>, <em>servertimemillis</em>).</li> <li>Should I change my deletion thread? It is purely deleting data for the tuple <em>userid</em>, greater than <em>specialtimestamp</em>.</li> </ol> <h3>Edit:</h3> <p>When reading the <a href="http://dev.mysql.com/doc/refman/5.1/de/innodb-transaction-isolation.html" rel="nofollow noreferrer">MySQL documentation</a>, I wonder if I cannot simply define the connection for inserting and deleting rows with</p> <pre><code>conn.setIsolationLevel( Connection.TRANSACTION_READ_COMMITTED ) </code></pre> <p>and achieve what I need. It says that UPDATE- and DELETE statements, that use a unique index with a unique search pattern only lock the matching index entry, <strong>but not the gap before</strong> and with that, rows can still be inserted into that gap. It would be great to get your experience on that, since I can't simply try it on production - and it is a big effort to simulate it on test environment.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload