row locking within ACID transaction innodb

Question

I have a financial transaction that looks something like this (pseudo):

begin
    select record for update
    update record(same record as selected)
    insert another record into another table
commit

If I call SELECT ... FOR UPDATE within the transaction, will the lock be released for the specific rows only when the transaction is committed ?

Answer 1

According to Page 418 Paragraph 3 of MySQL 5.0 Certification Study Guide

the following commands can break a transaction

• ALTER TABLE
• BEGIN
• CREATE INDEX
• DROP DATABASE
• DROP INDEX
• DROP TABLE
• RENAME TABLE
• TRUNCATE TABLE
• LOCK TABLES
• UNLOCK TABLES
• SET AUTOCOMMIT = 1
• START TRANSACTION

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.

Answer 2

Also LOOP if exception handler is out of loop and transaction scope is with in loop even then mysql breaks transaction upon exception, better declare exception handler if any at same scope transaction was declared.