[SOLVED] How to lock a table?

Yii 1.1 General Discussion
1

I have a simple online users counter. Now on each request, I check if the user ip is already in the table by trying to update the timestamp. If the entry couldn’t get updated I insert it. When refreshing the page very fast, $updated returns 0 (or in this case boolean false) for some reason. See code:




$updated = (bool)Yii::app()->db->createCommand("UPDATE `online` SET `timestamp` = UNIX_TIMESTAMP() WHERE `ip` = '{$ip}' LIMIT 1")->execute();


if (!$updated)

{

   Yii::app()->db->createCommand("INSERT INTO `online` (`ip`, `timestamp`) VALUES ('{$ip}', UNIX_TIMESTAMP())")->execute();

}

$ip is of course clients ip (in my case always 127.0.0.1). When refreshing several times I have many entries with same ip and same timestamp in the table. When putting a PRIMARY KEY on ip, I get a constraint error of course.

I don’t understand how it can actually return “0 entries updated” when there are already many entries in the table. How is that possible?

Now I tried to lock the table using this before the snippet above:


Yii::app()->db->createCommand("LOCK TABLES online WRITE")->execute();

and this after:


Yii::app()->db->createCommand("UNLOCK TABLES")->execute();

But doesn’t work. Table is InnoDB by the way. I tried the refreshing with MEMORY and MyISAM. All the same… $updated may return 0 in some cases even if there are 100 entries with same ip.

Can someone help?

// Okay I’ve just learned something. When trying to update something with the exact same values (in this case timestamp might be the same between several requests), mysql counts it as “the row was not affected” and $updated will return 0.

Now I did this instead and it works perfect:




Yii::app()->db->createCommand("

   INSERT INTO `online` (`ip`, `timestamp`)

      VALUES ('{$ip}', UNIX_TIMESTAMP())

         ON DUPLICATE KEY UPDATE `timestamp` = UNIX_TIMESTAMP()

")->execute();
2

Hi,

i also need to lock tables.

Is there a way to lock tables by using Yii-Models/Active Record?

Something like that:

$model=new MyTable();

$model->lock(‘write’);

…or something like that?

Because: using Yii::app()->db->createCommand… is very specific depending on the underlying Database, so $model-> is more abstract. (Is’n it? I thnk so)!

greeting, rall0r :slight_smile: