سعي

Transactionsالمعاملات

الشريحة 1 من 12

مقدمة الفصل

ماذا ستتعلم في هذا الفصل

  1. Transaction Concept
  2. Transaction State
  3. Concurrent Executions
  4. Serializability

أقسام الفصل

  1. 1Transaction conceptمفهوم المعاملة
  2. 2ACID propertiesخصائص ACID
  3. 3Transaction statesحالات المعاملة
  4. 4Concurrent executions and schedulesالتنفيذ المتزامن والجداول الزمنية
  5. 5Serializability and conflicting instructionsقابلية التسلسل والتعليمات المتعارضة
  6. 6Precedence graph and testing for conflict serializabilityمخطط الأسبقية واختبار قابلية التسلسل
  7. 7Transaction definition in SQLتعريف المعاملة في SQL

تلميح: استخدم أسهم لوحة المفاتيح للتنقل بين الشرائح.

تفاصيل المصدرالفصل 6 · الشريحة ⁦2⁩
المصدر
الفصل 6 · الشريحة ⁦2⁩

Transaction conceptمفهوم المعاملة

Transaction
A set of logical operations to access and update the various data items of a database.

Transfer $50 from account A to account B

  1. 1

    read(A)

  2. 2

    A := A − 50

  3. 3

    write(A)

  4. 4

    read(B)

  5. 5

    B := B + 50

  6. 6

    write(B)

  • Two main issues: failures of various kinds (hardware failures, system crashes) and concurrent execution of multiple transactions.
تفاصيل المصدرالفصل 6 · الشريحة ⁦3⁩
المصدر
الفصل 6 · الشريحة ⁦3⁩

ACID propertiesخصائص ACID

ACID properties and who maintains them (slides 4–9)
PropertyMeaning (slides 4–8)Responsible (slide 9)
AtomicityAll or nothing: the entire transaction takes place at once or doesn't happen at allTransaction Manager
ConsistencyFrom one consistent state to another (e.g., A + B stays 300)Application programmer
IsolationTransactions occur independently, without interferenceConcurrency Control Manager
DurabilityCompleted updates persist despite software or hardware failuresRecovery Manager

Which component is responsible for maintaining the isolation property?

تفاصيل المصدرالفصل 6 · الشرائح ⁦4–9⁩
المصدر
الفصل 6 · الشرائح ⁦4–9⁩

ACID in the $50 transferACID في مثال التحويل

ACID properties

ماذا يحدث في كل حالة؟

  1. Atomicity

    A = 200, B = 100. The transaction fails after step 3 (A = 150 written) and before step 6.

  2. Consistency

    What must hold before and after the transfer?

  3. Isolation

    Between steps 3 and 6, T2 runs read(A), read(B), print(A+B).

  4. Durability

    The user has been told the transfer is done, then the system crashes.

Between write(A) and write(B) of T1, transaction T2 prints A + B and gets 250 instead of 300. Which requirement was not met?

تفاصيل المصدرالفصل 6 · الشرائح ⁦5–8⁩
المصدر
الفصل 6 · الشرائح ⁦5–8⁩

Transaction statesحالات المعاملة

Active
The initial state; stays here while executing.
Partially committed
After the final statement has been executed.
Failed
After discovering that normal execution can no longer proceed.
Aborted
After the transaction has been rolled back and the database restored to its state prior to the start. Then: restart (only if no internal logical error) or kill.
Committed
After successful completion.
  • Diagram (slide 11): active → partially committed → committed; active → failed; partially committed → failed; failed → aborted.

A transaction has executed its final statement, but its result is not yet confirmed. Which state is it in?

تفاصيل المصدرالفصل 6 · الشرائح ⁦10–11⁩
المصدر
الفصل 6 · الشرائح ⁦10–11⁩

Concurrent executionsالتنفيذ المتزامن

Advantages (slide 12)

  • Increased processor and disk utilization, leading to better transaction throughput — e.g., one transaction uses the CPU while another reads from or writes to the disk.
  • Reduced average response time: short transactions need not wait behind long ones.
Concurrency control schemes
Mechanisms to achieve isolation: control the interaction among concurrent transactions so they do not destroy the consistency of the database.

A long report transaction and a short update transaction run concurrently, so the short one finishes quickly instead of waiting. Which advantage is this?

تفاصيل المصدرالفصل 6 · الشريحة ⁦12⁩
المصدر
الفصل 6 · الشريحة ⁦12⁩

Schedulesالجداول الزمنية

Schedule
A sequence of instructions specifying the chronological order in which instructions of concurrent transactions are executed: it must consist of all instructions of those transactions and preserve the order within each transaction. A completed transaction ends with commit; a failed one with abort.
T1: transfer $50 from A to B; T2: transfer 10% of the balance from A to B

Schedule 1 (serial)

  • T1: read(A), A := A − 50, write(A), read(B), B := B + 50, write(B), commit
  • then T2: read(A), temp := A * 0.1, A := A − temp, write(A), read(B), B := B + temp, write(B), commit

Schedule 3 (not serial)

  • T1: read(A), A := A − 50, write(A)
  • T2: read(A), temp := A * 0.1, A := A − temp, write(A)
  • T1: read(B), B := B + 50, write(B), commit
  • T2: read(B), B := B + temp, write(B), commit
  • Equivalent to Schedule 1
  • Schedule 2 is serial with T2 followed by T1. In schedules 1, 2 and 3, the sum A + B is preserved.

Which of these is a requirement of a schedule?

تفاصيل المصدرالفصل 6 · الشرائح ⁦13–16⁩
المصدر
الفصل 6 · الشرائح ⁦13–16⁩

Serializability and conflicting instructionsقابلية التسلسل والتعارض

Serializable schedule
A (possibly concurrent) schedule that is equivalent to a serial schedule (each transaction preserves consistency, so serial execution does). Forms: conflict serializability and view serializability.

Two operations conflict if ALL hold (slide 18)

  • They belong to different transactions
  • They operate on the same data item
  • At least one of them is a write operation
Conflict serializable
S can be transformed into S' by a series of swaps of non-conflicting instructions → S and S' are conflict equivalent; S is conflict serializable if it is conflict equivalent to a serial schedule.

Which pair of operations conflicts?

تفاصيل المصدرالفصل 6 · الشرائح ⁦17–19⁩
المصدر
الفصل 6 · الشرائح ⁦17–19⁩

Conflict serializable or not?أمثلة قابلية التسلسل

Serializability and conflicting instructions

Schedule 3

  • Can be transformed into Schedule 6 (serial, T2 follows T1) by swaps of non-conflicting instructions
  • Conflict serializable

T3 / T4 (slide 21)

  • T3: read(Q) … write(Q); T4: write(Q) in between
  • Cannot swap to obtain < T3, T4 > or < T4, T3 >
  • Not conflict serializable

T3 reads Q, then T4 writes Q, then T3 writes Q. Why can't this schedule be made serial by swaps?

تفاصيل المصدرالفصل 6 · الشرائح ⁦20–21⁩
المصدر
الفصل 6 · الشرائح ⁦20–21⁩

Precedence graphمخطط الأسبقية

Precedence graph
A directed graph whose vertices are the transactions; an arc from Ti to Tj if the two transactions conflict and Ti accessed the data item earlier; the arc may be labelled with the item.
Test
A schedule is conflict serializable if and only if its precedence graph is acyclic. The serializability order is then a topological sorting of the graph.
  • Slide 22's example has arcs T1 → T2 and T2 → T1 — a cycle.
  • Cycle detection takes order n² time (n vertices); better algorithms take order n + e (e edges).

The precedence graph of a schedule has arcs T1 → T2, T2 → T3 and T3 → T1. What can you conclude?

تفاصيل المصدرالفصل 6 · الشرائح ⁦22–23⁩
المصدر
الفصل 6 · الشرائح ⁦22–23⁩

Transaction definition in SQLالمعاملة في SQL

  • In SQL, a transaction begins implicitly.
  • Commit work commits the current transaction and begins a new one; Rollback work causes the current transaction to abort.
  • By default, every SQL statement also commits implicitly if it executes successfully; this can be turned off, e.g., JDBC connection.setAutoCommit(false).

In SQL, which statement commits the current transaction and begins a new one?

تفاصيل المصدرالفصل 6 · الشريحة ⁦24⁩
المصدر
الفصل 6 · الشريحة ⁦24⁩

الخلاصة

أنهيت الدرس

الخطوة التالية: ثبّت المعلومات بالبطاقات، ثم اختبر نفسك بالأسئلة.

البطاقاتالتدريب

ما راجعته في هذا الدرس

تفاصيل المصدر
المصدر
الفصل 6

خريطة الفصل

    1. 1مقدمة الفصل
    1. 2Transaction conceptمفهوم المعاملة

  3. ACID propertiesخصائص ACID

    1. 3ACID propertiesخصائص ACID
    2. 4ACID in the $50 transferACID في مثال التحويل
    1. 5Transaction statesحالات المعاملة

  5. Concurrent executions and schedulesالتنفيذ المتزامن والجداول الزمنية

    1. 6Concurrent executionsالتنفيذ المتزامن
    2. 7Schedulesالجداول الزمنية

  6. Serializability and conflicting instructionsقابلية التسلسل والتعليمات المتعارضة

    1. 8Serializability and conflicting instructionsقابلية التسلسل والتعارض
    2. 9Conflict serializable or not?أمثلة قابلية التسلسل

  7. Precedence graph and testing for conflict serializabilityمخطط الأسبقية واختبار قابلية التسلسل

    1. 10Precedence graphمخطط الأسبقية
    1. 11Transaction definition in SQLالمعاملة في SQL
    1. 12الخلاصة