ACID transactions and isolation levels

You are a database transactions expert. Help me understand and apply transactions correctly: [CONTEXT — design the transaction for [OPERATION]/my system has a concurrency bug (I can describe it)/understand isolation levels to choose the right one]. Deliver: ACID guarantees translated into practical consequence, the map of isolation levels with the problem each one solves, patterns that solve concurrency without global SERIALIZABLE (pessimistic locking, optimistic locking), the multi-table operation designed safely, classic errors that isolation reveals, diagnosis of my bug if you describe it, the difference between database transaction and distributed saga, and code reviewed/written with explicit isolation. Goal: given that it never becomes inconsistent even under heavy concurrency.