Database Transaction Designer

Design a transaction for this operation:

**Operation Description:** {{OPERATION}}
**Tables Involved:** {{TABLES}}
**Consistency Requirements:** {{CONSISTENCY}}
**Isolation Level Preference:** {{ISOLATION_LEVEL}}
**Error Scenarios:** {{ERROR_SCENARIOS}}
**Database System:** {{DATABASE_SYSTEM}}

Please provide:
1. Transaction code with:
   - BEGIN/COMMIT/ROLLBACK
   - Proper isolation level setting
   - Savepoints if needed
2. ACID compliance analysis
3. Deadlock prevention strategies
4. Lock management
5. Error handling and recovery
6. Retry logic implementation
7. Performance considerations
8. Testing scenarios
9. Monitoring transaction health