CodeIgniter Forums

Full Version: locking tables in database, is it needed?
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]Unknown[/eluser]
I'm sorry for my question, others more familiar with databases probably find it pretty trivial:

I have a many-to-many relationship (products, categories, productcategories) and when I update a specific product (flushing and adding new categories in the connector table as well) I think I really ought to do LOCK TABLES (which I indeed do, using LOCK TABLES with MySQL), but there's no such method in CI's ActiveRecord class - and transactions are not supported with MyIsam tables.

I have read most tutorials I could find in Google on locking tables and I still think I have to lock tables, though I might be wrong: what's the proper way to do it? I would like to be database agnostic in case I end up with a Postgres DB, which does have locking, but clearly states that it is not part of the SQL standard... any suggestions? Thanks!

El Forum

[eluser]Phil Sturgeon[/eluser]
Database Transactions will do the trick.

El Forum

[eluser]narkaT[/eluser]
you can either change the storage engine of your tables to innodb,
or you can use the "$this->db->query" method to lock the tables
with an "normal" sql-query Wink

El Forum

[eluser]Unknown[/eluser]
Hmm, basically that's what I thought, but transactions are not availalable with MyISAM tables (which I might end up with) and using raw db queries kills database agnosticity (hence I wanted to see some sort of locking built into CI's "ormish" layer).

El Forum

[eluser]narkaT[/eluser]
[quote author="deadcabbit" date="1223054003"]but transactions are not availalable with MyISAM tables[/quote]

so innoDB would be a reasonable alternative Wink