[database] How do ACID and database transactions work?

To quote Wikipedia:

ACID (atomicity, consistency, isolation, durability) is a set of properties that guarantee database transactions are processed reliably.

A DBMS that supports transactions will strive to support all of these properties - any commercial DBMS (as well as several open-source DBMSs) provide full ACID 'support' - although it's often possible (for example, with varying isolation levels in MSSQL) to lessen the ACIDness - thus losing the guarantee of fully transactional behaviour.