How to lock a table during an operation use php/yii

Question

My table:

CREATE TABLE IF NOT EXISTS `detail_transaction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `code` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `id_product` int(11) NOT NULL,
  `id_store` int(11) NOT NULL,
  `input_quality` int(11) NOT NULL,
  `output_quality` int(11) NOT NULL,
  `quality_in_store` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)

I have the problem is below:

I get record have max_id, and then before i insert new record, having another process insert new record was inserted.

I think: I will lock table during "I select a record have max_id" to "I completed to insert new next record"(don't run any task work with this table). And to do this method. Please help me! How to do this by php code or Yii.

score 1 · Accepted Answer · edited May 23 '17 at 12:07

1

You could use transactions:

$transaction = Yii::app()->db->beginTransaction();
try {
    foreach ($items as $item) {
        $item->attr = value;
        $item->save();
    }
    $transaction->commit();
    // actions to do on success (redirect, alert, etc.)
} catch (Exception $e) {
    $transaction->rollBack();
    // other actions to perform on fail (redirect, alert, etc.)
}

This source code is from this post: using transaction in a loop in yii

edited May 23 '17 at 12:07

Community

1
1

answered Oct 31 '14 at 09:49

Germanaz0

914
8
18

How to set isolationLevel for transaction – MrTo-Kane Nov 11 '14 at 07:11

score 1 · Answer 2 · answered Dec 24 '17 at 17:28

1

In Yii2, you can lock/unlock a table like this

$db = Yii::$app->getDb();
$db ->createCommand('LOCK TABLES `YOUR_TABLE` WRITE')->execute();
// access YOUR_TABLE here 
// something like YOUR_TABLE_MODEL::find()->where(["something" => "blah"])->one()
$db ->createCommand('UNLOCK TABLES')->execute();

answered Dec 24 '17 at 17:28

chenzen

63
1
1
7

Add some description – Mathews Sunny Dec 24 '17 at 17:51

score 0 · Answer 3 · answered Oct 31 '14 at 09:49

0

I'm not exactly sure what you want to achieve, but I'm sure it will work out if you just use transactions - http://www.yiiframework.com/doc-2.0/yii-db-transaction.html. Otherwise, you can always call a LOCK TABLE query - http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html.

$connection = Yii::app()->db;
$lock = $connection-> createCommand('LOCK TABLES `detail_transactions` WRITE');
// do your magic
$unlock = $connection-> createCommand('UNLOCK TABLES');

answered Oct 31 '14 at 09:49

motanelu

3,945
1
14
21

How is difference between use "Lock Table" and "Transaction"? – MrTo-Kane Nov 03 '14 at 04:31
A transaction makes a set of queries atomic. Read more here http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html – motanelu Nov 03 '14 at 10:35

How to lock a table during an operation use php/yii

3 Answers3