PHP mysqli : Transaction , bindParam

Transaction 

단계 : start  ➡️. RW operations ➡️ commit or abort

 

ACID property

• Atomicity : All or nothing
• Consistency : Only valid data will be written to the database (referntial intergirty)
• Isolation : Transcations (occurring at the same time) do not impact each other
• Durability : Transcations committed to the database will not be lost

 

Concurrency Control

Concurrency Control을 통해 서로 다른 transaction의 read/write를 공존할 수 있다

• Locking and timestamp-based concurrency control 가능
  • Oracle's Locking
    • Row-level locking, table locks
    • Automatic deadlock detection and rollback
• Oracle's multiversion concurrency control은 다른 회사들 것과 다름
  • statement나 transaction을 시작할 때, timestamp 역할을 하는 SCN(system change number)를 할당
  • query 과정에서 query보다 높은 SCN을 가진 data block이 발견되면, query를 초과하지 않는 가장 높은 SCN을 가진 데이터 블록의 이전 버전을 사용해야 함

Recovery

• Redo : 실행이 완료되지 않은 transcation을 재시도
• Undo : 실행이 완료되지 않은 transcation을 삭제

<Oracle Basic Structures for Recovery>

• control file에 백업 정보를 포함하여 DB 작동에 필요한 다양한 metadata 포함
• database buffer의 transcation 수정에 관한 log records를 redo
• Rollback segment에 그 데이터의 이전 버전 정보를 포함

<Recovery Procedure>

1.  백업에 redo log를 적용하여 roll foward (redo log에 commit되지 않은 데이터가 포함되어 불일치 상태)
2.  rollback segment를 사용하여 uncommitted transcation을 roll back (consistent state)

<Managed standby databases>

high availablity를 보장하기 위해

standby database는 별도의 시스템에 설치된 일반 데이터베이스의 복사본으로.

 

---

• $mysqli->begin_transaction();
• $stmt = $mysqli->$prepare('??query??');
• $stmt->bind_param('datatype for each ?', 변수들...);
• $stmt->execute();
• $mysqli->commit();  or $mysqli->rollback();
• with  ( mysqli_sql_exception $e )

---

begin_transaction

Object Oriented style

<?php
/* Tell mysqli to throw an exception if an error occurs */
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$mysqli = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* The table engine has to support transactions */
$mysqli->query("CREATE TABLE IF NOT EXISTS language (
    Code text NOT NULL,
    Speakers int(11) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

/* Start transaction */
$mysqli->begin_transaction();

try {
    /* Insert some values */
    $mysqli->query("INSERT INTO language(Code, Speakers) VALUES ('DE', 42000123)");

    /* Try to insert invalid values */
    $language_code = 'FR';
    $native_speakers = 'Unknown';
    $stmt = $mysqli->prepare('INSERT INTO language(Code, Speakers) VALUES (?,?)');
    $stmt->bind_param('ss', $language_code, $native_speakers);
    $stmt->execute();

    /* If code reaches this point without errors then commit the data in the database */
    $mysqli->commit();
} catch (mysqli_sql_exception $exception) {
    $mysqli->rollback();

    throw $exception;
}

 

 

Procedural style

<?php

/* Tell mysqli to throw an exception if an error occurs */
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$mysqli = mysqli_connect("localhost", "my_user", "my_password", "world");


/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* The table engine has to support transactions */
mysqli_query($mysqli, "CREATE TABLE IF NOT EXISTS language (
    Code text NOT NULL,
    Speakers int(11) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

/* Start transaction */
mysqli_begin_transaction($mysqli);

try {
    /* Insert some values */
    mysqli_query($mysqli, "INSERT INTO language(Code, Speakers) VALUES ('DE', 42000123)");

    /* Try to insert invalid values */
    $language_code = 'FR';
    $native_speakers = 'Unknown';
    $stmt = mysqli_prepare($mysqli, 'INSERT INTO language(Code, Speakers) VALUES (?,?)');
    mysqli_stmt_bind_param($stmt, 'ss', $language_code, $native_speakers);
    mysqli_stmt_execute($stmt);

    /* If code reaches this point without errors then commit the data in the database */
    mysqli_commit($mysqli);
} catch (mysqli_sql_exception $exception) {
    mysqli_rollback($mysqli);

    throw $exception;
}

 

 

---

bind_param

Object Oriented Style

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');

$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
$stmt->bind_param('sssd', $code, $language, $official, $percent);

$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;

$stmt->execute();

printf("%d row inserted.\n", $stmt->affected_rows);

/* Clean up table CountryLanguage */
$mysqli->query("DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d row deleted.\n", $mysqli->affected_rows);

?>

 

 

Procedural style

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$link = mysqli_connect('localhost', 'my_user', 'my_password', 'world');

$stmt = mysqli_prepare($link, "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'sssd', $code, $language, $official, $percent);

$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;

mysqli_stmt_execute($stmt);

printf("%d row inserted.\n", mysqli_stmt_affected_rows($stmt));

/* Clean up table CountryLanguage */
mysqli_query($link, "DELETE FROM CountryLanguage WHERE Language='Bavarian'");
printf("%d row deleted.\n", mysqli_affected_rows($link));
?>