0

We have queues of Ticket objects persisted to the database. Our entity containing the queues looks like this:

@Entity
public class StoreQueueCollection {

    @Id
    private int storeId;

    @ManyToMany(fetch=FetchType.LAZY)
    @OrderColumn
    private List<Ticket> mainQueue = new ArrayList<Ticket>();

    @ManyToMany(fetch=FetchType.LAZY)
    @OrderColumn
    private List<Ticket> cancelledQueue = new ArrayList<Ticket>();

    .. etc

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).

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.

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:

- 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

All locks are LockModeType.PESSIMISTIC_WRITE and scope is PessimisticLockScope.EXTENDED.

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.

Any suggestions on what we're doing wrong are greatly appreciated!

bsa
  • 2,671
  • 21
  • 31
  • Looks like problem with your locking. Can you provide simplified sample of your locking and transaction boundary code? – gerrytan Nov 12 '14 at 03:25
  • Thanks for the reply. Unfortunately it's a bit large and spread out across methods. – bsa Nov 12 '14 at 06:06
  • Logging at the SQL level (using log4jdbc-log4j2), it definitely looks like the locking isn't working as expected. Looks like I need to read the documentation again. – bsa Nov 14 '14 at 07:26

1 Answers1

0

Are you locking single rows, or are all of your threads locking a common 'queue' row? If the latter, keep in mind that you need to maintain a transaction for the entire time you want the lock to be held. If the former, locking single (different) rows won't do anything to prevent interleaving operations.

You didn't mention what backend you're using, but sometimes database logging tools (like SQL Server's profiler and activity monitor) can be helpful for determining what is going on, since you can get an ordered list of all SQL statements issued to the database.

T.D. Smith
  • 984
  • 2
  • 7
  • 22
  • Tyler, we are locking on an instance of the StoreQueueCollection entity class using EntityManager.refresh(storeQueueCollection, ...) and EntityManager.flush() to unlock. Monitoring the low level SQL is a good suggestion (we are using MySQL). – bsa Nov 12 '14 at 06:05
  • Can you confirm that the lock is truly working by setting a breakpoint after the lock is acquired, then trying to acquire it in some other thread? In my experience with Hibernate, I've found that all parties must acquire a lock for it to be effective. If one transaction gets a lock but another doesn't bother, the other transaction will be allowed to proceed. This is different from file locking in Windows, where locking a file causes all other writes to fail. – T.D. Smith Nov 12 '14 at 21:13
  • Tyler, I logged at the SQL level (using log4jdbc-log4j2), and it definitely looks like the locking isn't working as expected. Looks like I need to read the documentation again. – bsa Nov 14 '14 at 07:24