使用Doctrine和Azure SQL进行并发读取的数据库悲观锁定

Question

WHAT I'M TRYING TO DO

I have a cloud application with multiple worker nodes that can each process n export requests. Export requests are placed into tables in our database. Each export request should only ever be run once. If a worker node is currently running less than n export requests, it checks every 0.1 - 1 seconds to see if there's a export request it can start processing.

The export requests that are waiting to be run are organised into a priority queue on the database side using a stored procedure. The below function (queuePop()) is the function that calls the database. It gets the top result from the priority queue, marks the export request as being used (it's consider being used when the status is no longer set to 'NEW') and then returns the export request to be processed elsewhere.

public function queuePop() {

    $entity_manager->getConnection()->beginTransaction();

    // Get every new export request
    try {

        $statment = $entity_manager->getConnection()->prepare( 'EXEC root.findAllNewExportRequests' );
        $statment->execute();
        $results = $statment->fetchAll();

        if ( count( $results ) > 0 ) {

            $queue_item = array (
                    'export_request_id' => $results[0]['request_id']
            );
        } else {

            $entity_manager->getConnection()->commit();
            return NULL;
        }

        // Return the export request if it exists
        $export_request = $entity_manager->find( 'Application\Model\ExportRequest', $queue_item['export_request_id'], \Doctrine\DBAL\LockMode::PESSIMISTIC_READ );

        if ( !$export_request ) {

            // Export request not found
            throw new Exception( 'Export request not found.' );
        }

        // Update the export request
        $export_request->setStatus( 'QUEUED' );

        // Update the export request in the database
        $entity_manager->persist( $export_request );
        $entity_manager->flush();

        $entity_manager->getConnection()->commit();
        return $queue_item;
    } catch ( \Exception $ex ) {

        $entity_manager->getConnection()->rollback();
        exit();
    }
}

THE PROBLEM

The queuePop function works great and it ensures that a single worker node will never run an export request more than once, but when there's 2 or more nodes there's a chance that more than one worker node will pick up the request (it happens often enough given the number of requests it gets). I tried implementing pessimistic locking as seen here (http://doctrine-orm.readthedocs.org/en/latest/reference/transactions-and-concurrency.html#pessimistic-locking) but it isn't working. The docs aren't very good and I can't find any examples of pessimistic locking being used properly.

I need for when one worker node is reading/updating an export request, that the other nodes can't (they have to wait).

WHAT I'M USING

• nginx version: nginx/1.4.6 (Ubuntu)
• Ubuntu 14.04.1 LTS (GNU/Linux 3.13.0-32-generix x86_64)
• PHP 5.5.9-1ubuntu4.3 (fpm-fcgi)
• Zend Engine v2.5.0 with Zend OPcache v7.0.3
• Doctrine 2
• Azure SQL

Answer 1

You should be using Pessimistic Write (Doctrine\DBAL\LockMode::PESSIMISTIC_WRITE), locks the underlying database rows for concurrent Read and Write Operations. while PESSIMISTIC_READ only prevents the row from being updated but not from being read