Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This is a great question. InnoDB is a row level locking engine, but it has to set additional locks to ensure safety with the binary log (used for replication; point in time recovery). To start explaining it, consider the following (naive) example:</p> <pre><code>session1&gt; START TRANSACTION; session1&gt; DELETE FROM users WHERE is_deleted = 1; # 1 row matches (user_id 10), deleted. session2&gt; START TRANSACTION; session2&gt; UPDATE users SET is_deleted = 1 WHERE user_id = 5; # 1 row matches. session2&gt; COMMIT; session1&gt; COMMIT; </code></pre> <p>Because statements are only written to the binary log once committed, on the slave session#2 would apply first, and would produce a different result, <strong>leading to data corruption</strong>.</p> <p>So what InnoDB does, is sets additional locks. If <code>is_deleted</code> is indexed, then before session1 commits nobody else will be able to modify <em>or insert into the range</em> of records where <code>is_deleted=1</code>. If there are no indexes on <code>is_deleted</code>, then InnoDB needs to lock every row in the entire table to make sure the replay is in the same order. You can think of this as <em>locking the gap</em>, <strong>which is different concept to grasp from row-level locking directly</strong>.</p> <p>In your case with that <code>ORDER BY position ASC</code>, InnoDB needs to make sure that no new rows could be modified between the lowest key value and a "special" lowest possible value. If you did something like <code>ORDER BY position DESC</code>.. well, then nobody could insert into this range.</p> <p><strong>So here comes the solution:</strong></p> <ul> <li><p>Statement based binary logging sucks. I really look forward to a future where we all switch to <a href="http://dev.mysql.com/doc/refman/5.1/en/replication-formats.html" rel="noreferrer">row based binary logging</a> (available from MySQL 5.1, but not on by default).</p></li> <li><p>With Row-based replication, if you change the isolation level to read-committed, then only the one row that matches needs to be locked.</p></li> <li><p>If you want to be a masochist, you can also turn on <a href="http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog" rel="noreferrer">innodb_locks_unsafe_for_binlog</a> with statement-based replication.</p></li> </ul> <hr> <p><strong>Update 22 April</strong>: To copy + paste my improved version of your testcase (it was not searching 'in the gap'):</p> <pre><code>session1&gt; CREATE TABLE test (id int not null primary key auto_increment, data1 int, data2 int, INDEX(data1)) engine=innodb; Query OK, 0 rows affected (0.00 sec) session1&gt; INSERT INTO test VALUES (NULL, 1, 2), (NULL, 2, 1), (5, 2, 2), (6, 3, 3), (3, 3, 4), (4, 4, 3); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 session1&gt; start transaction; Query OK, 0 rows affected (0.00 sec) session1&gt; SELECT id FROM test ORDER BY data1 LIMIT 1 FOR UPDATE; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) session2&gt; INSERT INTO test values (NULL, 0, 99); # blocks - 0 is in the gap between the lowest value found (1) and the "special" lowest value. # At the same time, from information_schema: localhost information_schema&gt; select * from innodb_locks\G *************************** 1. row *************************** lock_id: 151A1C:1735:4:2 lock_trx_id: 151A1C lock_mode: X,GAP lock_type: RECORD lock_table: `so5694658`.`test` lock_index: `data1` lock_space: 1735 lock_page: 4 lock_rec: 2 lock_data: 1, 1 *************************** 2. row *************************** lock_id: 151A1A:1735:4:2 lock_trx_id: 151A1A lock_mode: X lock_type: RECORD lock_table: `so5694658`.`test` lock_index: `data1` lock_space: 1735 lock_page: 4 lock_rec: 2 lock_data: 1, 1 2 rows in set (0.00 sec) # Another example: select * from test where id &lt; 1 for update; # blocks </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow many rows will be locked by SELECT ... ORDER BY xxx LIMIT 1 FOR UPDATE?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMorgan Tocker
    1. USMorgan Tocker
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow many rows will be locked by SELECT ... ORDER BY xxx LIMIT 1 FOR UPDATE?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat if I don't use any replication at all? And did I get you right about of ORDER BY is used, the whole index range gets locked and therefore the whole table is locked as well?
      singulars
      1. PO
      1. USVladislav Rastrusny
    2. COIf you don't use replication, or the binary log for point-in-time-recovery, then set innodb_locks_unsafe_for_binlog (point in time recovery is so good though - I don't know why you wouldn't). It depends on the ORDER BY as to how much of the table is locked (from an internal perspective it's never a table lock, but individual locks could be set on each row if you don't have an index).
      singulars
      1. PO
      1. USMorgan Tocker
    3. COI should note, that you need the new InnoDB Locks - http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-table.html information_schema tables to debug what locking is happening.
      singulars
      1. PO
      1. USMorgan Tocker
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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