How to lock a mysql table for reading and writing in yii 1.1.16?

Question

MySQL

How to lock a mysql table for reading and writing in yii 1.1.16?
engine: innobd

Answer 1

Your question is slightly intimidating. Such issues are resolved long before the backend. Issuing and revoking rights to tables. Read the MySQL documentation in the GRANT query syntax section

Answer 2

1. Write permissions for the MySQL user

REVOKE *.* ON <database>.<table> FROM <username>@localhost;
GRANT SELECT, REFERENCE ON <database>.<table> TO <username-readonly>@localhost; -- только SELECT c JOIN

From Yii, you connect to the database with the user <username-readonly>. This method will prohibit the creation/modification/deletion of table entries for which rights have not been granted via GRANT to the user <username-readonly>
Not very flexible. The administrator probably would like to write to all tables.
2. Permissions for current Yii user via ACL or RBAC
www.yiiframework.com/wiki/346/acl-and-rbac
Not very reliable. You can forget about checking in some place.
3. You can tinker with CActiveRecord or CMysqlCommandBuilder so that queries containing INSERT / UPDATE / DELETE in some tables will return false or throw an exception if the current user is not an admin.
Already better, but parsing SQL queries will be difficult.

Answer 3

Wanguya has a large presence of "wise men" above, I tend to this option.

Yii::app()->db->createCommand('LOCK TABLES table READ, table AS t WRITE');
....
Yii::app()->db->createCommand('UNLOCK TABLES');