Re: Reply
Posted: Tue Jan 30, 2007 6:04 pm
Well, you don't need to use INNODB for Locking and transactions.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.
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.
If you are working with any kind of financial data is definitely is a very, very bad thing.feyd wrote:Is it catastrophic for a selection to receive older data (that hasn't been updated yet)?