InnoDB versus MyISAM

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Re: Reply

Post by AKA Panama Jack »

user___ wrote:On the other hand there is something else which must be taken into consideration about Locking(As you have said) and transactions. I have read an article about this stuff and the way that was described to cope with Locking with MyISAM was to create a lot of additional tables(Each table that can be deleted from should have an additional one according to them). They say that if you have a table with (Let say topics) then if you want to delete a topic from it you need to insert its id in another table(That contains only ids that have been deleted so far) and then by Join to extract only these topics whose ids do not belong to the table that contains the deleted ids.
Well, you don't need to use INNODB for Locking and transactions.

INNODB has automatic ROW LOCKING. This is where a row of data in a table is locked and cannot be accessed until the initial query is finished with the row. Once the query finishes with the data in that row then another query can access it. Other queries can still access data in the table.

MYIASM doesn't have automatic ROW LOCKING but it does have MANUAL TABLE LOCKING. This will lock the entire table and allow only the queries sent by the process that locked the table access. All other processes are locked out until the initial process sends an unlock table query or the initiating process ends.

The problem with TABLE LOCKING is nothing can access the data in the table until the initial locking process is finished. With INNODB row locking other processes can access data in the table as long as the other processes do not need the information from the locked row. If a query needs access to the locked row it waits until the row is unlocked. If you need any kind of data locking using INNODB tables is definitely the way to go. You can still perform similar actions using MYISAM with TABLE LOCKING but the database accesses may be slower.

And what you are talking about with extra tables is using MySqls temporary table system.

http://dev.mysql.com/tech-resources/art ... n-ch5.html
You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.
feyd wrote:Is it catastrophic for a selection to receive older data (that hasn't been updated yet)?
If you are working with any kind of financial data is definitely is a very, very bad thing.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Re: Reply

Post by feyd »

AKA Panama Jack wrote:If you are working with any kind of financial data is definitely is a very, very bad thing.
True, but this probably isn't financial data. ;)
user___
Forum Contributor
Posts: 297
Joined: Tue Dec 05, 2006 3:05 pm

Reply

Post by user___ »

I am sorry about my late response but it is morning in my country now and some hours ago when I was online it was too late.
feyd: I hope that it is not catastrophic for selections if the data is not updated(I am developing a portal that has nothing to do with money).
Post Reply