title: How to insert a column to make it auto increment according to another column
body: <p>There is the table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>type</th>
  <th>serial_no</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>apple</td>
  <td>1</td>
  </tr>
  <tr>
  <td>2</td>
  <td>banana</td>
  <td>1</td>
  </tr>
  <tr>
  <td>3</td>
  <td>banana</td>
  <td>2</td>
  </tr>
  <tr>
  <td>4</td>
  <td>apple</td>
  <td>2</td>
  </tr>
  <tr>
  <td>5</td>
  <td>water</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want the <code>serial_no</code> to be auto increment for every <code>type</code></p>
  <p>I tried</p>
  <pre><code>INSERT INTO MY_TBALE
  (type, serial_no)
  VALUES
  (
  apple, 
  (SELECT (COALESCE(MAX('type'), 0)+1 FROM MY_TABLE T1 WHERE type=apple)
  )
  </code></pre>
  <p>, but it seems not working on multi-threads system.
  I got some duplicate <code>serial_no</code> on same <code>type</code>.</p>