Why Convex Limits Transactions and How Concurrency Control Shapes Your Database

Database concurrency control is explained using a metaphor of accountants managing ledgers in a castle vault to illustrate transaction limits and conflicts. Pessimistic concurrency, similar to PostgreSQL, involves locking records during a transaction, which ensures consistency but can lead to contention, queues, and deadlocks. In contrast, optimistic concurrency control (OCC), used by systems like Convex, allows processes to read data, compute, and then attempt to write, retrying only if the data has changed in the interim. This OCC approach avoids tying up database resources and keeps the system responsive, but it requires handling potential retries when transactions conflict.