Duplicate value insertion, when using isolation level serializable

Question

I have a table SalesOrders, which holds the salesOrder for a particular organization. Because I track invoices across many organizations, the table also contains an invoice. Per-organization, the salesOrder should increment by one, without gaps.

I need to do a

INSERT INTO SalesOrders (...) 
VALUES (...
, SELECT MAX(salesOrder) + 1 
    FROM SalesOrders 
    WHERE organization = @org)

But this isn't safe without ISOLATION LEVEL SERIALIZABLE or equivalent locks.

However, when I wrap within a transaction with that isolation level, I'm seeing duplicate values inserted into my table. I don't understand why: the transaction isolation level should prevent this?

The block of code I'm using (PHP PDO) is below.

What reasons could there be for me to see duplicate salesNumbers in my table, as a result of this query?

$db->query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');

$db->beginTransaction();
$query = '
    SELECT  MAX(orderNumber) AS number
    FROM    SalesOrders
    WHERE   organization = :organization';

$params = array(':organization' => $organizationId);

$statement = $db->prepare($query);
$statement->execute($params);

// Sometimes we have no results at all [no sales for a particular organization]
$existing = $statement->fetchAll();
$newNumber = (sizeof($existing) == 1 ? $existing[0]['number'] + 1 : 1);

$createQuery = 'INSERT INTO SalesOrders (
                    organization,
                    orderNumber,
                    invoice
                ) VALUES (
                    :organization,
                    :number,
                    :invoice
                )';
$createParams = array(
    ':organization' => $organizationId,
    ':number' => $newNumber,
    ':invoice' => $invoiceId
);

$secondStatement = $db->prepare($createQuery);
$secondStatement->execute($createParams);

$db->commit();

More information:

The table in question has ~4300 rows, with 11 duplicated values. For example:

id      organization    invoice salesOrderNumber
12830   43              12975   705
12831   43              12976   705

Here's the scenario that fails: 1) first client executes the `SELECT`. 2) second client executes the `SELECT`. (This is not blocked by the first client even under `SERIALIZABLE`, since the `SELECT`s are compatible and both take shared locks.) 3) first client performs `INSERT` and commits (locks taken by the `INSERT` are released). 4) second client performs `INSERT` and commits. To fix this, use `WITH (HOLDLOCK)` or `WITH (SERIALIZABLE)` as a table hint in the first `SELECT` (and then putting the whole transaction under `SERIALIZABLE` is optional). — Jeroen Mostert, Jul 04 '18 at 06:50
@JeroenMostert: Isn't that the situation where a deadlock occurs? After `T1` and `T2` have finished their `SELECT`, they both have S-locks on the table. `T1` can't perform an `INSERT` (requiring the raising of an `X-lock`) whilst `T2` maintains it's `S-lock` on the table (thus [deadlock](https://pablocastilla.wordpress.com/2012/01/19/deadlocks-in-serializable-transactions-with-sql-server/)). This is the basis of my question: two transactions interleaving as you've described _should deadlock_, because otherwise they violate 'serializability', do they not? — simont, Jul 04 '18 at 07:14
Locks are usually as granular as possible, so we're actually taking row locks and range locks rather than table locks (otherwise an `INSERT` would be blocked if even one single `SELECT` was active in a transaction somewhere). To be honest, I'm not clear on the kinds of locks the first `SELECT` takes. It should range lock on `organization = :organization`, and then, as you rightly say, any `INSERT` on another session for that organization should block. (This is not a deadlock situation since it's waiting for the same lock to upgrade.) — Jeroen Mostert, Jul 04 '18 at 07:33
Try inserting a sleep/break after the `SELECT` (or just replay the statements in Management Studio) and use `sp_lock` to get a list of what's being locked, and whether or not the `INSERT` is blocked. I find my ability to reason about locking is quickly overwhelmed, and actually observing them works better. — Jeroen Mostert, Jul 04 '18 at 07:41

Duplicate value insertion, when using isolation level serializable

0 Answers0