How to lock tables with codeigniter?

Question

I have to run this sql routine in a model:

$this->db->query('LOCK TABLE orders WRITE');
$this->db->query('TRUNCATE TABLE orders');
$this->db->query('INSERT INTO orders SELECT * FROM orders_tmp');
$this->db->query('UNLOCK TABLES');

but I get this error:

Error Number: 1192
Impossible to execute the requested command: tables under lock or transaction running
TRUNCATE TABLE orders

I use MyISAM as DB engine on this table.

Could you please help me?

Answer 1

try this

$this->db->query('TRUNCATE TABLE orders');
$this->db->query('LOCK TABLE orders WRITE');
$this->db->query('INSERT INTO orders SELECT * FROM orders_tmp');
$this->db->query('UNLOCK TABLES');

Answer 2

To perform many INSERT and SELECT operations on a table real_table when concurrent inserts are not possible, you can insert rows into a temporary table temp_table and update the real table with the rows from the temporary table periodically. This can be done with the following code:

mysql> LOCK TABLES real_table WRITE, temp_table WRITE;

Kindly ask if it not worked for you.