title: Why do gaps appear in a List backed by @ManyToMany with @OrderColumn
body: <p>We have queues of Ticket objects persisted to the database. Our entity containing the queues looks like this:</p>
  
  <pre><code>@Entity
  public class StoreQueueCollection {
  
      @Id
      private int storeId;
  
      @ManyToMany(fetch=FetchType.LAZY)
      @OrderColumn
      private List&lt;Ticket&gt; mainQueue = new ArrayList&lt;Ticket&gt;();
  
      @ManyToMany(fetch=FetchType.LAZY)
      @OrderColumn
      private List&lt;Ticket&gt; cancelledQueue = new ArrayList&lt;Ticket&gt;();
  
      .. etc
  </code></pre>
  
  <p>We have one operation which moves a ticket from one queue to another (call this changeStatus), and another which adds a new ticket to the end of a queue (call this newTicket).</p>
  
  <p>When the two operations interleave on the same queue, the operations basically work, but we end up with a "gap" in our queue. In the database, this looks like a missing index in the order column of the table, like this: 0, 1, 2, 4. When the queue is loaded back into Java, the missing index becomes a null element in the queue.</p>
  
  <p>We are using pessimistic locking on the StoreQueueCollection object to prevent inconsistent interleaved updates, but it's not working as we would expect. With extra logging, we see strange sequences like this:</p>
  
  <pre>
  - changeTicketStatus() starts
  -   lock the queue using entityManager.refresh()
  -   ticket X removed from front of queue A
  -   queue is entityManager.flush()ed
  
  * newTicket() starts
  *   creates a new ticket Y, locks the StoreQueueCollection using entityManager.refresh();
  *   ticket Y added to the end of queue A
  *   ticket Y has fields initialized and is save()d
  *   call refresh(), method is blocked
  
  - changeTicketStatus() resumes
      (printing in-memory queue shows that ticket X is not in queue A)
  -   ticket X added to another queue B
  -   ticket X has some fields modified
  -   ticket X is saved using repository.save()
      (printing in-memory queue shows that ticket X is not in queue A)
  - changeTicketStatus() completes
  
  * newTicket() resumes
  *   refresh() returns
      (printing in-memory queue A shows that ticket X is still in queue!)
  *   ticket Y is added to end of queue A
      (printing in-memory queue A shows that ticket X and Y are in the queue)
  *   queues are save()d
  </pre>
  
  <p>All locks are LockModeType.PESSIMISTIC_WRITE and scope is PessimisticLockScope.EXTENDED.</p>
  
  <p>After this sequence of execution, an assertion triggers from another thread which checks for null entries in the queue. Mysteriously, the queue is basically correct (X is deleted, Y is added to the end), but there is a gap in the order column before Y.</p>
  
  <p>Any suggestions on what we're doing wrong are greatly appreciated!</p>