GK One-Line Question & Answer

Nodes proactively push probe messages along wait-for edges toward the transaction being waited for; if a probe returns to its origin a cycle (deadlock) is detected; avoids the need for a centralized global wait-for graph

Deadlock prevention eliminates one of the Coffman conditions (no advance knowledge needed). Deadlock avoidance requires each transaction to declare maximum resource needs in advance; the system uses this to determine if granting a request could lead to deadlock and denies unsafe requests

Coarser granularity (table locks): fewer locks per transaction = fewer potential conflicts = fewer deadlocks but less concurrency. Finer granularity (row locks): more locks per transaction = more potential for circular wait = higher deadlock probability but higher concurrency

Best practices: (1) Acquire locks in consistent order across all transactions. (2) Keep transactions short (fewer locks held for less time). (3) Access data in same order. (4) Use appropriate isolation level. (5) Use SELECT FOR UPDATE to acquire all needed locks upfront. (6) Retry logic for deadlock errors

2PL guarantees serializability but does NOT prevent deadlocks; in fact 2PL can cause deadlocks because transactions hold locks during the shrinking phase while waiting for new locks - deadlock prevention or detection must be combined with 2PL separately

A visual representation of transactions as nodes and lock wait relationships as directed edges; cycles indicate deadlocks; helps identify: which transactions are involved which specific rows/tables are the contention points what SQL statements caused conflicts and patterns suggesting application-level fixes

InnoDB has automatic deadlock detection using a wait-for graph; when detected the transaction with less undo log is chosen as victim and rolled back; deadlock information is accessible via SHOW ENGINE INNODB STATUS or the performance_schema.data_lock_waits table

Mutual exclusion means resources (data items) can only be held by one transaction at a time in write mode; for READ locks mutual exclusion can be eliminated (multiple readers allowed) via shared locks reducing deadlock probability; but write locks inherently require mutual exclusion for correctness

Application-level deadlock: occurs in application code (e.g. Thread A holds Java lock L1 waiting for Java lock L2; Thread B holds L2 waiting for L1) without involving database locks - the database itself is not deadlocked but the application threads are stuck. Different from DB deadlock where DBMS transactions hold DB locks circularly

A resource allocation algorithm that determines whether granting a resource request leaves the system in a safe state; requires: for each transaction - maximum resource needs declared in advance current allocation and remaining need; safe state = exists a sequence in which all transactions can complete using available resources

Hold-and-wait: a transaction holds one or more resources (locks) while waiting to acquire additional resources it needs. Elimination strategies: (1) Require all-or-nothing lock acquisition (acquire ALL needed locks at once before starting) (2) Release all current locks before requesting new ones (3) Use timeout-based retry after releasing

No preemption means that resources (locks) cannot be forcibly taken from a transaction holding them. Preemption can be introduced by: aborting (preempting) a deadlocked transaction (victim selection) which is how deadlock resolution works - the DBMS forcibly rolls back a chosen victim transaction releasing its locks for other waiting transactions

Detection frequency: how often the deadlock detector runs (e.g. every 100ms or every N lock requests); Detection latency: how long a deadlock persists before being detected and resolved (= time to next detection run + detection + abort time). Trade-off: higher frequency = lower latency but higher CPU overhead from running detection; lower frequency = lower overhead but deadlocked transactions blocked longer

Cycle prevention: design the system so that cycles in the wait-for graph cannot form (e.g., lock ordering, Wait-Die, Wound-Wait, no-wait) - proactive approach that adds overhead to every transaction. Cycle detection: allow cycles to form then detect and break them periodically (e.g., wait-for graph cycle detection) - reactive approach lower per-transaction overhead but deadlocks persist briefly

innodb_deadlock_detect=ON (default): InnoDB actively detects deadlocks using a wait-for graph and immediately resolves them by aborting a victim. innodb_deadlock_detect=OFF: deadlock detection is disabled; the lock wait timeout (innodb_lock_wait_timeout) becomes the only mechanism to break deadlocks - transactions wait until timeout then abort. Disabling detection reduces overhead in very high-concurrency scenarios but increases worst-case deadlock resolution time.

In SAGA-based distributed transactions traditional database deadlocks at the local level can still occur within each services database; additionally SAGA introduces higher-level semantic deadlocks where multiple sagas wait for each other to complete compensating actions or where resource locks across services create circular dependencies - resolved by designing sagas to acquire resources in consistent order and implementing timeouts for saga steps

RBAC: privileges are assigned to roles (logical groups) and roles are assigned to users; changing a roles privileges automatically affects all users with that role. Direct grants: privileges assigned directly to individual users making management complex and error-prone at scale

RLS is a database feature where security policies are automatically applied to queries at the database level based on the executing user/role transparently filtering rows without requiring application code or view modifications; views require explicit usage and can be bypassed if user has direct table access

Database-level encryption where data files log files and backups are encrypted on disk automatically by the DBMS without application changes; protects against: theft of physical storage media unauthorized access to database file copies but does NOT protect against authorized database users or SQL injection

Static data masking: permanently replaces sensitive data with realistic but fictitious values in non-production copies. Dynamic data masking: applies masking rules at query time for specific users returning masked values while original data remains stored unchanged

DAC: resource owners control access permissions (GRANT/REVOKE by object owner) - flexible but owners can grant to anyone. MAC: system-enforced labels on data and users (e.g. TOP SECRET data accessible only by TOP SECRET cleared users) owner cannot override - used in government/defense systems

Recording and monitoring database activities for security analysis compliance and forensics; key events to audit: all DDL changes privilege grants/revokes failed login attempts access to sensitive tables (PII financial) privileged user actions and unusual query patterns

Application database accounts should have only the minimum privileges required for their specific function: SELECT only for read-only applications INSERT/UPDATE/DELETE on specific tables for write operations no DDL permissions no GRANT permissions no access to system tables or other schemas

Encryption at rest: protects data stored on disk (TDE filesystem encryption) from physical theft/unauthorized file access. Encryption in transit: protects data moving between client and database server (TLS/SSL) from network eavesdropping. Both needed because threat model has both physical and network vectors

A security vulnerability where an attacker gains higher privileges than intended e.g. through SQL injection granting admin access exploiting stored procedure definer rights or GRANT OPTION abuse. Prevented by: least privilege principle removing unnecessary privileges auditing privilege assignments

Restricting access to specific columns rather than entire tables; implementations: (1) View-based: create views that SELECT only permitted columns. (2) Column-level privileges: GRANT SELECT(col1 col2) ON table TO user. (3) Column-level encryption: columns encrypted with keys only authorized users possess

Combining multiple non-sensitive queries or pieces of information to deduce sensitive information that is not directly accessible; e.g. if a statistical query reveals the average salary of a 1-person department that reveals the single persons salary even if individual salary queries are blocked

Tokenization: replacing sensitive data (credit card numbers) with randomly generated tokens that have no mathematical relationship to the original data; tokens are stored in a secure token vault. Encryption: mathematically transforms data using a key (can be decrypted with key). Tokenization cannot be reversed without vault access

A mathematical framework that adds calibrated noise to query results providing a formal guarantee: the probability of learning any specific fact about an individual is nearly the same whether or not that individuals data is in the database (epsilon-differential privacy)

A privacy model ensuring each record is indistinguishable from at least k-1 other records with respect to quasi-identifying attributes; limitations: vulnerable to homogeneity attacks (if all k records have same sensitive value) and background knowledge attacks

Allows the grantee to further grant the same privilege to other users; security risk: privilege can propagate uncontrollably (Alice grants to Bob WITH GRANT OPTION Bob grants to 100 other users without DBA knowledge) creating uncontrolled access expansion that is difficult to audit and revoke

An independent security layer that monitors all database traffic in real-time without relying on native DBMS audit features; benefits over native auditing: cannot be disabled by DBAs (separation of duties) lower performance impact (passive network monitoring) cross-DBMS policy enforcement real-time alerting

A systematic security assessment of database configurations and code identifying: misconfigured privileges (public grants excessive DBA accounts) weak authentication (default passwords weak policies) unpatched software (known CVEs) insecure configurations (remote root login unnecessary features enabled) SQL injection vulnerabilities in stored procedures

REVOKE privilege FROM user CASCADE: also revokes the privilege from all users who received it from that user via GRANT WITH GRANT OPTION (cascading revocation); security concern: unexpected cascade may revoke privileges from many users not intended for revocation

Post-incident analysis of database systems to reconstruct what happened: examining transaction logs (redo/undo) audit trails access logs network packet captures OS logs and potentially deleted/recovered data to determine the sequence of events identify the attacker and assess the scope of a breach

A security layer that sits between the application and the database server monitoring and filtering SQL statements in real-time based on whitelisted allowed query patterns blocking anomalous or malicious SQL - detects SQL injection unauthorized queries and policy violations

The process of categorizing data based on its sensitivity level (e.g. public internal confidential restricted/top-secret) so that appropriate security controls can be applied proportionally - critical because it determines access controls encryption requirements audit levels backup policies and regulatory compliance obligations

The process of reducing the attack surface of a database by applying security best practices: removing unnecessary features/users/sample databases changing default passwords patching known vulnerabilities disabling dangerous SQL functions restricting network access and following vendor security guidelines (e.g. CIS Benchmarks)

A security principle that requires splitting privileged operations among multiple people so no single person can perform all steps of a sensitive operation alone - prevents insider threats and requires collusion for fraud. Implemented by: separating DBA roles (application DBA vs security DBA) keeping DBAs out of application user tables using DAM tools that DBAs cannot disable

A list of the most critical security risks for web applications; key database-related vulnerabilities include: (1) Injection (SQL injection is most common) (2) Broken access control (unauthorized data access) (3) Security misconfiguration (default passwords public grants) (4) Sensitive data exposure (unencrypted PII) and (5) Insufficient logging and monitoring (no audit trail)