CODEWITHSUNDEEP
mysqlcodeigniterlocking
user1534715
user1534715

Reputation: 41

How to lock tables with codeigniter?

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?

Upvotes: 4

Views: 10986

Answers (3)

Madbreaks
Madbreaks

Reputation: 19549

You're required to acquire a lock for all tables in your query, not just the table you're writing to. So in your case you also need a read lock on orders_tmp.

From the docs:

A session that requires locks must acquire all the locks that it needs in a single LOCK TABLES statement. While the locks thus obtained are held, the session can access only the locked tables.

Docs here: https://dev.mysql.com/doc/refman/5.5/en/lock-tables.html

Cheers

Upvotes: 1

user3934078
user3934078

Reputation: 21

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');

Upvotes: 2

Arun Jain
Arun Jain

Reputation: 5464

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.

Upvotes: 1

Related Questions