Database Concurrency, Locking, and Optimization Techniques

Posted on Dec 25, 2024 in Computers

Concurrency Control in Databases

Concurrency control addresses issues arising when multiple transactions access a shared database simultaneously. Key problems include:

• Lost Update Problem: Occurs when two transactions update the same record concurrently, leading to one update being overwritten.
• Uncommitted Dependency: Happens when a transaction reads data modified by another uncommitted transaction.
• Inconsistent Analysis: Arises when a transaction reads multiple values, and another transaction modifies some of those values during the first transaction’s execution.

Conditions for Concurrency Issues

Concurrency problems typically occur when:

• Multiple transactions access a shared resource.
• Transactions conflict on data access and overlap in time.
• There is a lack of proper isolation levels or locking mechanisms.

Serializability ensures that concurrent transactions leave the database in the same state as if they had executed serially.

Concurrency Control Schemes

• Pessimistic Schemes: Prevent conflicts by assuming they are common.
• Optimistic Schemes: Allow transactions to execute and check for conflicts only at commit time.

Database Isolation Levels

DR – read data not yet committed, NRD – reads row twice two values, PR – different rows same query

Locking Mechanisms

• Exclusive (Write) Lock: Held by only one transaction at a time.
• Share (Read) Lock: Permits reading but not updating; multiple shared locks are allowed on the same item.

Deadlock Prevention

1. Prevent transactions from getting into a deadlock.
2. Permit only older (or younger) transactions to wait for younger (or older) ones.

An update lock is a hybrid lock type designed to prevent deadlocks when transactions need to both read and then potentially modify a resource. It serves as a middle ground between share (S) and exclusive (X) locks. Allows a transaction to read a resource with the intention to modify it later. Only one transaction can hold an update lock.

Intent Locks: Allow the database to quickly determine whether higher-level locks are compatible with a requested lock. When an X lock is placed on a row, an intent X lock is placed on the column.

Database Security and Optimization

ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME 60; or LIMIT FAILED_LOGIN_ATTEMPTS 3; or PASSWORD_REUSE_MAX 5;

Cost-based optimization is a query optimization method used by databases, like Oracle, to determine the most efficient execution plan for a SQL query. It works by evaluating different possible execution plans and selecting the one with the lowest estimated cost, where “cost” refers to a combination of factors such as CPU time, I/O operations, memory usage, and other system resources required to execute the query.

CREATE USER joe DROP USER joe CASCADE; User’s sys privs are not specified here

IDENTIFIED BY joespassword CREATE SESSION GRANTED PRIV BY DEFAULT

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp

QUOTA 10M ON users;

Creating a profile for joe create profile sales_mgr

• 1 session sessions_per_user 1
• 100 seconds of cpu per session cpu_per_session 10000
• 30 minutes of idle before disconnect idle_time 30
• Max of 8 hours per sessions connect_time 480
• 10 seconds of cpu max per call cpu_per_call 1000
• Allow up to 5 failed login attempts failed_login_attempts 5
• If account locks, unlock in 2 hours password_life_time 60
• Password must be changed every 60 days password_lock_time 2/24

Object Privileges – select, insert, delete – GRANT SELECT ON employees TO joe WITH GRANT;

System Privileges – create session/table, alter system, drop/create user, grant privileges

Object Privs revocation cascade, sys privs do not. If first granter is revoked, grantees are also revoked

Database Backup and Recovery

The export operation is used to create a logical backup of a database or specific database objects in a logical binary dump file along with a log file that details the process.

Export/import is useful for backups, migration, transport between dbs, optimization. No export data files.

export table exp username/password FILE=exp_tables.dmp LOG=exp_tables.log TABLES=schema_name.table1,schema_name.table2

Direct path load/unload bypasses SQL and loads directly into database blocks.

External Table Path Load allows querying data stored outside of Oracle (e.g., CSV files).

Data pump is used for moving data between databases, while SQL*Loader is for loading data from external files.

• Logical Backup: For schemas and objects, useful for migrations, slower to recover.
• Physical Backup: Full database recovery, requires more storage, point-in-time recovery.

ARCHIVELOG mode is a configuration in which Oracle archives the filled redo log files before they are reused. This enables the database to recover to a point in time using archived redo logs.

Inconsistent Backups can occur due to missing redo logs, backups with ongoing transactions, or using backups without a control file.

Backup Types

• Cold (offline) Database Backup: Shut down the database, back up all Oracle files (control files, redo log files, data files, configuration files), then start up Oracle.
• Hot (online) Database Backup: Requires the use of ARCHIVELOG mode.

Recovery strategy: Multiplex control and redo log files, operate in ARCHIVELOG mode, have physical and logical backups.

Database Performance Tuning

The process is: Business process -> DB design -> SQL -> Memory -> I/O

• Object stats: # rows, len rows, index stats, manual, can be automatic, static.
• System stats: I/O stats, CPU stats, mem stats, automatically done by Oracle, dynamic.

Automatic Workload Repository (AWR): Automatically gathers and stores statistics about the database instance approximately once every hour.

Automatic Database Diagnostic Monitor (ADDM): Compares AWR snapshots, detects potential performance problems, and makes recommendations.

Tune memory after you’ve tuned the application and SQL, but before you tune I/O.

Tune when users complain about performance, during upgrades, or when there is a change in workload.

Key areas for tuning: Business Rules, Logical Database Design, Application Design, Logical Database Structure, Database Operations, Access Paths, Memory Allocation, I/O and Physical Structure, Resource Contention, Underlying Platform.