StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can add FOR UPDATE to the select statement to lock all the rows it comes across.</p> <p>But it's not enough - you basically need to lock the entire table (because you are going to update the entire table), and transactions will not do it for you. innoDB has row level locking, and only the needed rows are locked, which will fail badly here because you are only selecting one row, but updating everything.</p> <p>SELECTing the entire table FOR UPDATE in order to lock all rows might lock everything, but it better be the very first SELECT you run. Otherwise you read one row, and freeze your view (consistent read), then another transaction does the same. Now both of you have the same view - but that second transaction actually needs to read the data that is just about to change! (The second transaction will not block - you are reading, and locking, different rows. The system does not know you are planning to update the entire table.) So if you lock in this way (FOR UPDATE), that statement must be the one to "freeze" your view of the table.</p> <p>After some research I decided that the only possible solution is advisory locking.</p> <p>(The regular LOCK command in MySQL does not work with transactions.)</p> <p>Use the GET_LOCK function instead - and run it <em>before</em> opening a transaction, not after.</p> <p>i.e.</p> <pre><code>SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT GET_LOCK('folders_nested_set', <timeout>); /* remember to check to make sure the lock worked */ START TRANSACTION WITH CONSISTENT SNAPSHOT; do work on folders table here COMMIT; DO RELEASE_LOCK('folders_nested_set'); </code></pre> <p>Make sure that all functions that work with the nested set are wrapped in transactions. Not for writing, but to ensure consistent reads across the SQL statements.</p> <p>i.e.</p> <pre><code>SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION WITH CONSISTENT SNAPSHOT; do reads here COMMIT; </code></pre> <p>Unless you know you are reading all the data you need in just one SQL statement, then you don't need this.</p> <p>(You can do SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; right after you connect to the db, instead of by every transaction. Make sure to include the SESSION keyword in that case.)</p> <p>If I am wrong, or missed something here - or even if there is a better way I would greatly appreciate knowing about it because I am dealing with exactly this same situation.</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