Yii2 Unique Validator not working when insert so fast

Question

I am using ActiveRecord model to save data.

Unique Validator works very perfect. But when i insert data so fast, it no longer works perfectly. In some request i get error that it can not catch by Yii.

Integrity constraint violation – yii\db\IntegrityException SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '***'...

Does we have any solution to handle this problem without adding another service ?

Thanks !

Answer 1

Summing up in the comments...

Most probably what you need is to lock table manually before validation and release lock after it. Yii2 provides optimistic locks mechanism but it is not suitable for your case. Optimistic locks are supported only inside update and delete methods:

Optimistic locking is only supported when you update or delete an existing row of data using yii\db\ActiveRecord::update() or yii\db\ActiveRecord::delete(), respectively.

Moreover what does optimistic lock is just raise exception when update fails due conflict (no actual table locking).

The solution will depends on your DB engine. Yii2 provides mutex mechanism for manual locking. Out of the box Yii2 supported Mysql and Postgres. See components description at following pages of Yii2 manual :

• Mysql mutex component
• Pgsql mutex component

So, after you configured your mutex in config (example for pgsql from official guide):

[
    'components' => [
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'pgsql:host=127.0.0.1;dbname=demo',
        ]
        'mutex' => [
            'class' => 'yii\mutex\PgsqlMutex',
        ],
    ],
]

you'll need to do something like that

\Yii::$app->mutex->acquireLock($lockingObject);

// validate uniqueness and save

\Yii::$app->mutex->releaseLock($lockingObject);

Or, for sure you can do it manually using SQL syntax of your RDBMS.

MySQL:

SELECT GET_LOCK('tablename',10);
SELECT RELEASE_LOCK('tablename');

Pqsql:

LOCK TABLE tablename IN SHARE ROW EXCLUSIVE MODE;

Be aware that Pgsql lock works only inside transaction.

Answer 2

• Unique Validator not work in this case

My solution for this problem (prevent duplicate insert):

• I use MySQL UNIQUE Index and INSERT IGNORE to ignore new record if exists. It easy to implement. And It work very fast.