According to Page 418 Paragraph 3 of MySQL 5.0 Certification Study Guide
the following commands can break a transaction
SET AUTOCOMMIT = 1
As long as you do not any of these commands inside the transaction, the transaction should be stable. In fact, SELECT FOR UPDATE is so strong that row locks invade the indexes as well as the data. It could even set off possible InnoDB deadlocks. I once wrote about that in this post : select for update gives error on indexed column
As long as you keep transactions short and sweet,
SELECT FOR UPDATE should not be a problem. If you want to protect your transaction from intermittent or unexpected deadlocks, MySQL 5.6 now has the following:
START TRANSACTION READ WRITE;
START TRANSACTION READ ONLY;
You should start the transaction with options like these. Why ?
According to that MySQL Documentation:
The READ WRITE and READ ONLY modifiers set the transaction access mode. They permit or prohibit changes to tables used in the transaction. The READ ONLY restriction prevents the transaction from modifying or locking both transactional and nontransactional tables that are visible to other transactions; the transaction can still modify or lock temporary tables. These modifiers are available as of MySQL 5.6.5.
Your last comment was
so can i lock rows within a transaction another way other then for update
You can also run SELECT ... LOCK IN SHARE MODE. The Documentation says
- SELECT ... LOCK IN SHARE MODE sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.
- For index records the search encounters, SELECT ... FOR UPDATE locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... LOCK IN SHARE MODE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.)
Although possible, keep transactions short. Otherwise, you can increase the likelihood of deadlocks.
UPDATE 2014-02-13 21:57 EST
To clear up any misconceptions, please note this from the MySQL Documentation:
All locks set by LOCK IN SHARE MODE and FOR UPDATE queries are released when the transaction is committed or rolled back.
Therefore, if you execute SELECT ... LOCK IN SHARE MODE inside a transaction, the rows stay locked until the transaction commits or rolls back.