Add transaction support (async with db.transaction()) + make get_or_create atomic

[1st]

Problem

There is no transaction API — only TODO comments. get_or_create even documents "not atomic; wrap in a transaction", but there is nothing to wrap it in. A production ORM needs explicit transactions.

Scope

• async with db.transaction(): context manager on BaseDatabase, implemented for Postgres (asyncpg) and MySQL (aiomysql) — BEGIN / COMMIT / ROLLBACK, nested via savepoints (or documented no-nesting for v1).
• Surface at a usable level (e.g. async with Model.transaction(): or registry-level) so multiple save()/bulk ops share one transaction/connection.
• Make get_or_create atomic when run inside a transaction; document concurrency guarantees.
• Tests (both backends): commit persists; exception rolls back; rollback leaves DB unchanged; get_or_create race-safety under a transaction.

Design

Capture the connection-vs-pool / per-task-connection model in an ADR — this constrains future work.

[1st]

Design accepted in ADR 0006 (merged #109). Implementation spec:

• Connection model: pool from day one — asyncpg.create_pool / aiomysql.create_pool replace the single shared connection in postgres.py/mysql.py; connect()/close() → pool create/close.
• Binding: a ContextVar[conn | None] per alias holds the current task's checked-out connection; the six BaseDatabase methods use it when set, else acquire a one-off pooled (autocommit) connection. models.py/queryset.py untouched.
• Nesting: real SAVEPOINTs (reentrant transaction()), rendered as raw SQL via the dialect for one cross-backend code path.
• Entry points (all four): db.transaction(), @atomic(alias=None), Model.transaction(), DatabaseRegistry.transaction(alias).
• MySQL: drop autocommit=True inside a transaction.
• Wire get_or_create to be atomic once available.
• Tests (both backends): commit persists; exception → rollback; nested savepoint inner-rollback leaves outer intact; concurrent transactions in separate tasks are isolated; @atomic decorator + each entry point.
• Pool config: minimal defaults; overrides later only if needed.

FastAPI usage (lifespan pool + per-request transaction dependency) tracked in #108.