Page 6 - SQL

Dirty read, non-repeatable read, and phantom read are the three concurrency anomalies the SQL standard uses as a reference when defining isolation levels. Each anomaly arises when two transactions overlap in time, and each can be reproduced with a concrete two-transaction timeline instead of only being described abstractly.

Most PHP developers only know PDO from query() and fetch(). Using PDO at an advanced level means controlling fetch modes deliberately, setting transaction boundaries with intent, and avoiding the typical performance traps in bulk operations that rarely show up in tutorials but appear daily in production code.

Hasura turns an existing PostgreSQL database into a complete GraphQL API within seconds, including filtering, sorting, pagination and relationships, without writing a single resolver. The approach fits certain scenarios extremely well and is completely unsuitable for others. This article shows exactly where that line runs.

The index exists, the column is selective, yet EXPLAIN shows a full table scan. That pattern almost always has one of a handful of concrete causes: a function on the indexed column, implicit type coercion, a leading wildcard, or stale statistics. This guide walks through a fixed order that narrows down the cause in minutes.

Isolation levels define how much a transaction is allowed to see of concurrently running, not-yet-completed transactions. The SQL standard defines four stages from Read Uncommitted to Serializable, each with a different trade-off between correctness and throughput, and every major database picks its own default.

LAG and LEAD solve a problem that used to almost always require a self-join: accessing the value of the previous or next row within a sorted order. Whether it is month-over-month comparisons, trend analyses, or detecting gaps in a sequence, this article explains the syntax, the offset and default parameters, and the practical use cases of LAG and LEAD in detail.

JSON columns give a relational database exactly the modeling freedom that otherwise serves as an argument for a NoSQL switch, without giving up transactions, joins, and referential integrity. Whoever correctly indexes, queries, and validates JSONB in PostgreSQL or JSON in MySQL covers many semi-structured use cases within the existing database.

Prepared statements stop classic SQL injection at the input field, but far from every vector runs through a single form field. Second-order injection, blind injection without a visible error message, out-of-band exfiltration via DNS, and injection in identifier contexts such as ORDER BY bypass exactly the safeguards teams usually rely on.

Approximate aggregation replaces exact calculations like COUNT DISTINCT or percentiles with probabilistic algorithms that deliver a result on billions of rows in a fraction of a second, with a controlled error usually under one percent. This article explains how HyperLogLog and t-digest work, when approximate aggregation pays off, and when exact calculation remains the right choice.

A good index strategy does not depend on which ORM or framework sits on top of the database, but on fundamental principles such as selectivity, column order and the deliberate use of covering indexes. Once you understand these principles, you make better indexing decisions regardless of whether the application is written in PHP, Java, Python or plain SQL.