# Database ACID characteristic

Database operation provides ACID characteristics.

* **Atomicity**: All operations either occur or fail and are treated as single instructions.
* **Consistency**: Any rules set (cascades, indexes, triggers) are correctly executed.
* **Isolation**: Concurrent behavior shall be the same as sequential behavior.
* **Durability**: Changes are persisted and shall not be lost, even with a DMBS crash.

Caveat:

* In the banking scenario, each access (GetBalance, SetNewBalanceToDB) follows ACID, but the database has no knowledge (or control) over the additional logic.

Databases provide notions additional mechanisms to enforce ACID with macro operations.

## CWE-362 – Example - Banking

### Locks

<figure><img src="https://3744219775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FUaTnynVhA2CYjsGJT14j%2Fuploads%2FBqHP8oAEN14R24H5Dbm0%2F2023-11-20_11-09.png?alt=media&#x26;token=61b110d9-3ae9-4d8f-9145-c86bff38ecac" alt=""><figcaption></figcaption></figure>

```php
LockDB();
$transfer_amount = GetTransferAmount();
$balance = GetBalance();

if ($transfer_amount < 0) {
    UnLockDB();
    FatalError("Bad Transfer Amount");
}

$nb = $balance - $transfer_amount;
if (($balance - $transfer_amount) < 0) {
    UnLockDB();
    FatalError("Insufficient Funds");
}

SetNewBalanceToDB($nb);
UnLockDB();
NotifyUser("Transfer of $transfer_amount succeeded.");
NotifyUser("New balance: $newbalance");
```

* DB is locked.
* No other operations take place.

### Transactions

<figure><img src="https://3744219775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FUaTnynVhA2CYjsGJT14j%2Fuploads%2FwCR1iz0olnidWY3HAItO%2F2023-11-20_11-09_1.png?alt=media&#x26;token=16328599-e03b-4bf1-9ce8-b8c535a5cda5" alt=""><figcaption></figcaption></figure>

```php
BeginTransaction();
$transfer_amount = GetTransferAmount();
$balance = GetBalance();

if ($transfer_amount < 0) {
    EndTransaction();
    FatalError("Bad Transfer Amount");
}

$nb = $balance - $transfer_amount;
if (($balance - $transfer_amount) < 0) {
    EndTransaction();
    FatalError("Insufficient Funds");
}

SetNewBalanceToDB($nb);
CommitTransaction();
NotifyUser("Transfer of $transfer_amount succeeded.");
NotifyUser("New balance: $newbalance");
```

* DB Operations are queued.
* Queue is discarded or committed atomically

### Versioning

```php
GetVersion();
$transfer_amount = GetTransferAmount();
$balance = GetBalance();

if ($transfer_amount < 0) {
    FatalError("Bad Transfer Amount");
}

$nb = $balance - $transfer_amount;
if (($balance - $transfer_amount) < 0) {
    FatalError("Insufficient Funds");
}

SetNewBalanceToDB($nb);
Commit();
NotifyUser("Transfer of $transfer_amount succeeded.");
NotifyUser("New balance: $newbalance");
```

At `Commit();` DB version is acquired. Commit may FAIL if another change took place.

## Improper Synchronization

```c
// Global
shared_object_t data;

void update_data(char* cookie, pthread_mutex_t * mutex) {
    pthread_mutex_lock(mutex);
    // Manipulate global data object
    pthread_mutex_unlock(mutex);
}
```

Direct solution:

* Protect changes with a mutex.

Developer assumes lock/unlock always work.

## CWE-362 – Race Condition – Isolated Ops

X86\_64: i++ with gcc

* ```asm6502
  add    DWORD PTR [rbp-4], 1
  ```

X86\_64: i++ with clang

* ```asm6502
  mov    edi, dword ptr [rbp - 8]
  ```
* ```asm6502
  add    edi, 1
  ```
* ```asm6502
  mov    dword ptr [rbp - 8], edi
  ```

ARM: i++

* ```armasm
  ldr    r3, [fp, #-8]
  ```
* ```armasm
  add    r3, r3, #1
  ```
* ```armasm
  str    r3, [fp, #-8]
  ```

Developer thinks: `i++` is a single operation.

In reality... it depends, and varies with the architecture.

Still (generic behavior).

* Value of “i" must be available (previous logic).
* Value must be fetched from RAM to Cache.
  * Page must be addressed and then loaded.
  * MMUs and other systems are used.
* Value must be fetched from cache to Register.
* Register as to be increased.
* Result must be stored in Cache.
* Result shall be committed to RAM.

## CWE-362 - Improper Synchronization

The following function attempts to acquire a lock to perform operations on a shared resource.

* The code does not check the value returned by `pthread_mutex_lock()` for errors.
* If **`pthread_mutex_lock()`** cannot acquire the mutex for any reason, the function may introduce a race condition into the program and result in undefined behavior.

```c
// Vulnerable
void f(pthread_mutex_t *mutex) {
    pthread_mutex_lock(mutex);
    /* access shared resource */
    pthread_mutex_unlock(mutex);
}
```

```c
int f(pthread_mutex_t *mutex) {
    int result;
    result = pthread_mutex_lock(mutex);
    if (0 != result)
        return result;
    /* access shared resource */
    return pthread_mutex_unlock(mutex);
}
```
