They focused mostly about concurrency and solving the issues that come with it, database transactions, atomicity, locks, etc.
Using the supplied transaction and lock-event records, write a PostgreSQL query that identifies transactions affected by rollback or positive lock waits. Include rolled-back transactions even when they have no matching lock events.
transaction_id, status, total_wait_ms, waited_resource_count, first_wait_at, last_wait_at, and concurrency_issuetotal_wait_ms descending, then transaction_id ascending| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique database transaction identifier |
| started_at | TIMESTAMP | Transaction start time |
| committed_at | TIMESTAMP | Commit time, null when not committed |
| status | VARCHAR(20) | Transaction outcome |
| isolation_level | VARCHAR(30) | Transaction isolation level |
| Column | Type | Description |
|---|---|---|
| lock_event_idPK | INT | Unique lock event identifier |
| transaction_id | INT | Transaction associated with the lock event |
| resource_key | VARCHAR(50) | Locked database resource |
| event_type | VARCHAR(20) | Lock lifecycle event type |
| event_at | TIMESTAMP | Time the lock event occurred |
| wait_ms | INT | Milliseconds spent waiting for the lock |