I have a transaction that does one read on the database, followed by one write. If I choose MyIsam as my database engine in MySQL does the table level locking mechanism of MyIsam guarantee me that, once the transaction starts its read, the table is locked from all other transactions?
I mean if there are two or more transactions, I want them in this order:
R W R W R W R W R W...
But if these are done in this order:
R R R R W W W W....
my database goes into an inconsistent state. This is the specific requirement in have on this particular table. Is making the table MYIsam sufficient? If not what else do I need to do? Is table level locking meant only for writes?
The MySQL documentation states that:
LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.
Beginning a transaction (for example, with START TRANSACTION) implicitly commits any current transaction and releases existing locks.
But those stipulations apply to a transactional engine (InnoDB). MyISAM doesn't support InnoDB style transactions.
MyISAM has read locks (which will prevent writes from any sessions), and write locks (which prevent reads and writes from other sessions). Multiple sessions can obtain read locks, but if one session obtains a write lock no other session can read, lock, or write to the table while the write lock is in place.
I encourage you to consult the docs on the topic, as well as those pages linked to at the top of the page I linked to. Sorry that the link refers to a specific version number; I don't know of a version-agnostic way to link to MySQL documentation.
It sounds to me like you need to obtain a write lock, and do your read/write while the lock is active, then release it, and move on to the next. If you were to first obtain a read lock, do your read, then release and obtain a write lock, you expose race conditions.