title: Can an INSERT operation result in a deadlock?
body: <p>Assuming:</p>
  
  <ul>
  <li>I am using REPEATABLE_READ or SERIALIZABLE transaction isolation (locks get retained every time I access a row)</li>
  <li>We are talking about multiple threads accessing multiple tables simultaneously.</li>
  </ul>
  
  <p>I have the following questions:</p>
  
  <ol>
  <li><strong>Is it possible for an <code>INSERT</code> operation to cause  a deadlock?</strong> If so, please provide a detailed scenario demonstrating how a deadlock may occur (e.g. Thread 1 does this, Thread 2 does that, ..., deadlock).</li>
  <li>For bonus points: answer the same question for all other operations (e.g. SELECT, UPDATE, DELETE).</li>
  </ol>
  
  <p><strong>UPDATE</strong>: 
  3. For super bonus points: how can I avoid a deadlock in the following scenario?</p>
  
  <p>Given tables:</p>
  
  <ul>
  <li>permissions<code>[id BIGINT PRIMARY KEY]</code></li>
  <li>companies<code>[id BIGINT PRIMARY KEY, name VARCHAR(30), permission_id BIGINT NOT NULL, FOREIGN KEY (permission_id) REFERENCES permissions(id))</code></li>
  </ul>
  
  <p>I create a new Company as follows:</p>
  
  <ul>
  <li>INSERT INTO permissions; -- Inserts permissions.id = 100</li>
  <li>INSERT INTO companies (name, permission_id) VALUES ('Nintendo', 100); -- Inserts companies.id = 200</li>
  </ul>
  
  <p>I delete a Company as follows:</p>
  
  <ul>
  <li>SELECT permission_id FROM companies WHERE id = 200; -- returns permission_id = 100</li>
  <li>DELETE FROM companies WHERE id = 200;</li>
  <li>DELETE FROM permissions WHERE id = 100;</li>
  </ul>
  
  <p>In the above example, the INSERT locking order is [permissions, companies] whereas the DELETE locking order is [companies, permissions]. Is there a way to fix this example for <code>REPEATABLE_READ</code> or <code>SERIALIZABLE</code> isolation?</p>