Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Sorry for the long answer, but this will need to be answered in multiple parts.</p> <p><strong>1. On locking InnoDB tables with <code>LOCK TABLES</code> in general</strong></p> <p>Using <code>LOCK TABLES</code> with InnoDB does in fact work, and can be demonstrated with two instances of the MySQL CLI connected to the same server (denoted by <code>mysql-1</code> and <code>mysql-2</code>) in the below example. It should generally be avoided in any sort of production context because of the impact to clients, but sometimes it can be the only option.</p> <p>Create a table and populate it with some data:</p> <pre><code>mysql-1&gt; create table a (id int not null primary key) engine=innodb; Query OK, 0 rows affected (0.02 sec) mysql-1&gt; insert into a (id) values (1), (2), (3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 </code></pre> <p>Lock the table:</p> <pre><code>mysql-1&gt; lock tables a write; Query OK, 0 rows affected (0.00 sec) </code></pre> <p>Try to insert from <code>mysql-2</code>, which will hang waiting on the lock:</p> <pre><code>mysql-2&gt; insert into a (id) values (4); </code></pre> <p>Now unlock the table from <code>mysql-1</code>:</p> <pre><code>mysql-1&gt; unlock tables; Query OK, 0 rows affected (0.00 sec) </code></pre> <p>And finally <code>mysql-2</code> unblocks and returns:</p> <pre><code>Query OK, 1 row affected (6.30 sec) </code></pre> <p><strong>2. Using phpMyAdmin for testing</strong></p> <p>Your testing method using phpMyAdmin is invalid because phpMyAdmin does not maintain a persistent connection to the server between queries from its web interface. In order to use any sort of locking <code>LOCK TABLES</code>, <code>START TRANSACTION</code>, etc., you need to maintain a connection while the locks are held.</p> <p><strong>3. Locking all tables needed during work</strong></p> <p>The way that MySQL locks tables, once you have used <code>LOCK TABLES</code> to explicitly lock anything, you will not be able to access any other tables that were not locked explicitly during the <code>LOCK</code> ... <code>UNLOCK</code> session. In your above example, you need to use:</p> <pre><code>LOCK TABLES my_table WRITE, new_table WRITE, table2 READ; </code></pre> <p>(I am assuming <code>table2</code> used in the subselect was not a typo.)</p> <p><strong>4. Atomic table swap using <code>RENAME TABLE</code></strong></p> <p>Additionally, I should note that replacing the existing table using <code>DROP TABLE</code> followed by <code>RENAME TABLE</code> will cause a brief moment where the table does not exist, and this may confuse clients that expect it to exist. It is generally much better to do:</p> <pre><code>CREATE TABLE t_new (...); &lt;Populate t_new using some method&gt; RENAME TABLE t TO t_old, t_new TO t; DROP TABLE t_old; </code></pre> <p>This will perform an atomic swap of the two tables.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow can I lock an InnoDB table to prevent updates while that table is being copied?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USjeremycole
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow can I lock an InnoDB table to prevent updates while that table is being copied?
      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. VTAcceptedByOriginator
    1. COexcellent response. thank you very much.
      singulars
      1. PO
      1. UScompcentral
 

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