4 ms·

Why would you use extra locking inside of a transaction? Transaction gives you the required atomicity already

• mjb 13y ago
  For Consistency (i.e. C, not I). Depending on the operations you are trying to perform and the isolation level you have chosen, locking may be the only way to update the database consistently, even within a single transaction. See this paper for a good explanation of how C and I interact: http://aphyr.com/data/posts/294/tr-95-51.pdf http://aphyr.com/data/posts/294/tr-95-51.pdf
  • ivanhoe 13y ago
    Not sure if we talk of the same thing? When I say transaction I mean a number of queries between START TRANSACTION and COMMIT (or ROLLBACK) SQL command, and mysql will lock automatically all tables inside that block. If you use SELECT inside the transaction innoDB will work on a data snapshot to insure the data consistency. (btw. thanks for the link I'll read it later)
    • ivanhoe 13y ago
      I stand corrected here, actually it seems that in the default repeatable-read mode START TRANSACTION will not do any table/row LOCK-ing automatically..
• kozlovsky 13y ago
  It depends on isolation level. Default isolation level in MySQL is REPEATABLE READ and not SERIALIZABLE. Without locks, lost updates are sometimes possible.
  • ivanhoe 13y ago
    thanks for the explanation, I guess I need to do some extra reading on this...
    • kozlovsky 13y ago
      The basic scenario where lost update is possible looks this way: session_1: BEGIN session_1: SELECT data from T1 where pk = 123 session_2: BEGIN session_2: SELECT data from T1 where pk = 123 session_2: UPDATE T1 set data = "value_2" where pk = 123 session_2: COMMIT session_1: UPDATE T1 set data = "updated_value" where pk = 123 session_1: COMMIT In this scenario, session_1 does not see "value_2", so "updated_value" does not take "value2" into account, and update of session_2 becomes lost. There are two ways to prevent this problem: 1) Using SELECT FOR UPDATE instead of SELECT. This way session_1 take exclusive lock on the corresponding row, and session_2 execution is suspended until session_1 will commit. 2) Using more strict SERIALIZABLE isolation level. In this mode, session_1 in MySQL will take shared lock on each selected row, and each SELECT effectively becomes SELECT ... LOCK IN SHARE MODE. This way session_2 also blocks until session_1 will commit. In PostgreSQL, SERIALIZED mode implemented the other way - SELECT works as usual (without locks), but during UPDATE session_1 will detect that the row was changed by concurrent transaction, and transaction of session_1 will be terminated with the exception "could not serialize access due to concurrent update".
      • Roboprog 13y ago
        Yes, yes, yes! Having the "late" commit (session 1) fail in this case is exactly what I would expect as "correct" behavior. However, I (now) see what MJB meant about needing an explicit (table/row) lock so that if you can start such a sequence, you should be able to complete it.