2

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 !

Nana Partykar
  • 10,556
  • 10
  • 48
  • 77
Ngô Văn Thao
  • 3,671
  • 1
  • 20
  • 24
  • What do you mean "insert so fast" ?? Unique validator works at server side , how is that affected by your data entry speed in client side !! – ck_arjun Jul 21 '16 at 03:29
  • @ck_arjun eg: I have a list of gift on website, user can click on receive button to receive gift. Each gift only can be received by one user. The problem is at the same time hundreds of people click on receive button. – Ngô Văn Thao Jul 21 '16 at 03:46
  • 1
    yii may be taking up time after validation and before save and its possible like in your case , same value getting saved to db. I don't know whether yii has any workaround. Will catching the exception solve your issue ?? – ck_arjun Jul 21 '16 at 05:11
  • 1
    Agree with @ck_arjun possible you should manually lock table before validation and unlock after save. What db engine do you use? – oakymax Jul 21 '16 at 06:09
  • 1
    Read up on [locking](http://stackoverflow.com/questions/31269160/how-to-implement-mysql-record-locking-in-yii2) – Brett Gregson Jul 21 '16 at 08:29
  • seems like Unique Validator is not suitable for this case – Ngô Văn Thao Jul 22 '16 at 07:13

2 Answers2

2

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 :

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.

oakymax
  • 1,454
  • 1
  • 14
  • 21
  • Thanks Maxim, Locking is a good solution but i think row-level locking is better – Ngô Văn Thao Jul 22 '16 at 05:01
  • @NgôVănThao yes, row-level is better. Please update this answer when you'll implement your solution. Put here working example. This can help somebody else – oakymax Jul 22 '16 at 09:08
0
  • 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.
Ngô Văn Thao
  • 3,671
  • 1
  • 20
  • 24