# فصل ۰۵ — نوشتن، تراکنش، و سرعت

<div dir="rtl">

# فصل ۰۵ — نوشتن، تراکنش، و سرعت

## در این فصل چه یاد می‌گیری

هزار سطر را دو جور می‌نویسیم. **دقیقاً همان هزار سطر، همان جدول، همان دستورِ `INSERT`** — و در اجرای این فصل یکی از دو راه صدها برابر بیشتر طول می‌کشد.

و این بار `EXPLAIN QUERY PLAN` هیچ کمکی نمی‌کند: نقشهٔ اجرای یک `INSERT` **کاملاً خالی** است. پس این فصل نشان می‌دهد وقتی ذره‌بینِ دوره ساکت است، سراغِ چه چیزِ دیگری باید رفت.

![دو مسیرِ کالسکه‌ای که یکی بعد از هر جعبه دروازه‌ای را می‌بندد و باز می‌کند و دیگری همهٔ جعبه‌ها را می‌برد و یک بار دروازه را می‌بندد](/lesson-assets/t5-s05-writes-and-transactions/prompt/01-image-gate-per-box.png)

آخر این فصل می‌توانی:

- با `UPDATE` و `DELETE` بنویسی و عددِ سطرهای تغییرکرده را بخوانی
- با `BEGIN` و `ROLLBACK` نشان بدهی «همه یا هیچ» یعنی چه
- تفاوتِ هزار `commit` با یک تراکنش را با دو عدد نشان بدهی
- `PRAGMA journal_mode` را عوض کنی و اثرش را اندازه بگیری
- خطای `database is locked` را بسازی، بخوانی، و رفعش کنی

## قبل از شروع

**از ترمِ ۱:** `INSERT` و `executemany` و پارامترِ `?`. **از ترمِ ۴:** `commit` بعد از هر نوشتن، و اینکه `executescript` تراکنشِ باز را قطعی می‌کند.

**دانه‌بندی:** یک سطر از `events` یک رویداد. جدولِ `queue` را همین فصل می‌سازد و یک سطرش یک یادداشتِ ساختگی است.

> 📓 **نوت‌بوک:** [نوت‌بوک این فصل را در Colab باز کن](/notebooks/avand/term-5-performance/session-05-writes-and-transactions.ipynb) — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

</div>

| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
| `events` | ۴۰۰٬۰۰۰ | چند ثانیه |
| `books` | ۴۰٬۰۰۰ | کمتر از یک ثانیه |
| `members` | ۱۲٬۰۰۰ | آنی |
| `queue` (همین فصل می‌سازدش) | ۰ تا ۱٬۰۰۰ | آنی |

<div dir="rtl">

## ۱. `UPDATE`، و عددی که برمی‌گرداند

هر `UPDATE` و `DELETE` یک عددِ مهم برمی‌گرداند که خیلی‌ها نادیده‌اش می‌گیرند: **چند سطر واقعاً عوض شد.** این عدد در `cursor.rowcount` است و **اولین شمارشِ کنترلیِ هر نوشتنی همین است.**

</div>

```python
print("جمع days در ابتدا:", int(q("SELECT SUM(days) AS s FROM events").iloc[0, 0]))

cursor = con.execute("UPDATE events SET days = days + 1 WHERE kind = ?", ("in",))
con.commit()
print("سطرهایی که عوض شدند:", cursor.rowcount)
print("جمع days بعد از UPDATE:", int(q("SELECT SUM(days) AS s FROM events").iloc[0, 0]))

back = con.execute("UPDATE events SET days = days - 1 WHERE kind = ?", ("in",))
con.commit()
print("سطرهایی که برگشتند:", back.rowcount)
print("جمع days دوباره:", int(q("SELECT SUM(days) AS s FROM events").iloc[0, 0]))
```

```text
جمع days در ابتدا: 8200275
سطرهایی که عوض شدند: 192169
جمع days بعد از UPDATE: 8392444
سطرهایی که برگشتند: 192169
جمع days دوباره: 8200275
```

<div dir="rtl">

**۸٬۳۹۲٬۴۴۴ منهای ۸٬۲۰۰٬۲۷۵ می‌شود دقیقاً ۱۹۲٬۱۶۹** — یعنی همان تعداد سطری که `rowcount` گفت، هرکدام یک واحد. دو عدد که مستقل از هم به‌دست آمدند و همدیگر را تأیید کردند.

و نقشهٔ اجرا برای یک `UPDATE` معنا دارد، چون پیدا کردنِ سطرها بخشی از کارش است:

</div>

```python
show_plan("UPDATE events SET days = days + 1 WHERE kind = ?", ("in",))
```

```text-approx
 id  parent      detail
  4       0 SCAN events
```

<div dir="rtl">

> ✅ **چک کن:** «جمع days دوباره» باید **دقیقاً** برابرِ «جمع days در ابتدا» باشد. اگر نبود، یکی از دو `UPDATE` سطرهای متفاوتی گرفته — و آن‌وقت داده‌ات را خراب کرده‌ای بدونِ اینکه خطایی گرفته باشی.

## ۲. `BEGIN` و `ROLLBACK`: «همه یا هیچ» یعنی چه

یک تراکنش یعنی چند تغییر که **یا همه‌شان اتفاق می‌افتند یا هیچ‌کدام.** اینجا با `DELETE` امتحانش می‌کنیم، چون خطرناک‌ترین دستورِ ممکن بهترین جا برای دیدنِ این خاصیت است:

</div>

```python
con.execute("BEGIN")
removed = con.execute("DELETE FROM events WHERE kind = ?", ("in",))
print("سطرهایی که DELETE برداشت:", removed.rowcount)
print("شمارش داخلِ تراکنش      :", con.execute("SELECT COUNT(*) FROM events").fetchone()[0])

con.rollback()
print("شمارش بعد از ROLLBACK   :", con.execute("SELECT COUNT(*) FROM events").fetchone()[0])
```

```text
سطرهایی که DELETE برداشت: 192169
شمارش داخلِ تراکنش      : 207831
شمارش بعد از ROLLBACK   : 400000
```

<div dir="rtl">

**داخلِ تراکنش، جدول واقعاً ۲۰۷٬۸۳۱ سطر داشت. بعد از `ROLLBACK`، انگار هیچ‌وقت چیزی حذف نشده بود.**

سه کلمه که کلِ ماجرا هستند:

1. **`BEGIN`** — از اینجا به بعد، تغییرها موقتی‌اند.
2. **`COMMIT`** — همه‌شان با هم قطعی می‌شوند.
3. **`ROLLBACK`** — همه‌شان با هم دور ریخته می‌شوند.

و **این خاصیت فقط برای امنیت نیست؛ کلیدِ سرعت هم هست** — که بخشِ بعد نشانش می‌دهد.

## ۳. چرا هزار `commit` گران است

جدولِ `queue` را می‌سازیم و هزار سطر را دو جور می‌نویسیم: یک بار با یک `commit` بعد از هر سطر، و یک بار با یک تراکنش برای همه.

</div>

```python
con.execute("CREATE TABLE queue (id INTEGER PRIMARY KEY, body TEXT NOT NULL)")
con.commit()
rows = [(number, f"یادداشت {number}") for number in range(1, 1001)]

start = time.perf_counter()
for row in rows:
    con.execute("INSERT INTO queue VALUES (?, ?)", row)
    con.commit()
one_by_one_ms = (time.perf_counter() - start) * 1000
print("سطرهای نوشته‌شده، راه اول:", int(q("SELECT COUNT(*) AS n FROM queue").iloc[0, 0]))

con.execute("DELETE FROM queue")
con.commit()

start = time.perf_counter()
con.executemany("INSERT INTO queue VALUES (?, ?)", rows)
con.commit()
one_transaction_ms = (time.perf_counter() - start) * 1000
print("سطرهای نوشته‌شده، راه دوم:", int(q("SELECT COUNT(*) AS n FROM queue").iloc[0, 0]))
print("تعداد commit در راه اول :", len(rows))
print("تعداد commit در راه دوم :", 1)
```

```text
سطرهای نوشته‌شده، راه اول: 1000
سطرهای نوشته‌شده، راه دوم: 1000
تعداد commit در راه اول : 1000
تعداد commit در راه دوم : 1
```

```python
print("هزار commit :", round(one_by_one_ms, 1), "میلی‌ثانیه")
print("یک تراکنش   :", round(one_transaction_ms, 2), "میلی‌ثانیه")
print("نسبت        :", round(one_by_one_ms / one_transaction_ms), "برابر")
```

```text-approx
هزار commit : 3131.8 میلی‌ثانیه
یک تراکنش   : 4.57 میلی‌ثانیه
نسبت        : 685 برابر
```

<div dir="rtl">

**همان هزار سطر، صدها برابر اختلاف.**

حالا ذره‌بینِ دوره را روی همان `INSERT` می‌گیریم:

</div>

```python
steps = plan("INSERT INTO queue VALUES (?, ?)", (2000, "آزمایش"))
print("تعداد سطرهای نقشهٔ اجرای این INSERT:", len(steps))
```

```text
تعداد سطرهای نقشهٔ اجرای این INSERT: 0
```

<div dir="rtl">

**نقشه خالی است، و این خودش جواب است.**

`EXPLAIN QUERY PLAN` می‌گوید پایگاه داده **چطور سطرها را پیدا می‌کند**. این `INSERT` با `VALUES` چیزی را پیدا نمی‌کند، پس نقشه‌ای هم ندارد. هزینه جای دیگری است: **هر `commit` باید مطمئن شود که تغییر روی دیسک نشسته**، و این یعنی صبر کردن برای سخت‌افزار. هزار بار صبر کردن، هزار برابرِ یک بار صبر کردن است — مهم نیست هر بار یک سطر بود یا هزار سطر.

**و قاعده‌ای که از این درمی‌آید تا آخرِ عمرِ کاری‌ات به دردت می‌خورد: `commit` را از داخلِ حلقه بیاور بیرون.**

## ۴. `PRAGMA journal_mode`: همان کار، با دفترِ ثبتِ دیگر

`SQLite` برای اینکه بتواند `ROLLBACK` کند، قبل از هر تغییر نسخهٔ قدیمی را جایی می‌نویسد. **حالتِ پیش‌فرض یک فایلِ جانبی می‌سازد و بعد از هر `commit` پاکش می‌کند.** حالتِ `WAL` به‌جایش تغییرها را به یک فایلِ پیوسته اضافه می‌کند و آن رفت‌وبرگشت را حذف می‌کند.

</div>

```python
con.execute("DELETE FROM queue")
con.commit()
print("حالت فعلی:", con.execute("PRAGMA journal_mode").fetchone()[0])
print("حالت جدید:", con.execute("PRAGMA journal_mode = WAL").fetchone()[0])

start = time.perf_counter()
for row in rows:
    con.execute("INSERT INTO queue VALUES (?, ?)", row)
    con.commit()
wal_ms = (time.perf_counter() - start) * 1000

print("سطرهای نوشته‌شده:", int(q("SELECT COUNT(*) AS n FROM queue").iloc[0, 0]))
con.execute("DELETE FROM queue")
con.commit()
print("برگشت به حالت اول:", con.execute("PRAGMA journal_mode = DELETE").fetchone()[0])
```

```text
حالت فعلی: delete
حالت جدید: wal
سطرهای نوشته‌شده: 1000
برگشت به حالت اول: delete
```

```python
print("هزار commit در حالت پیش‌فرض:", round(one_by_one_ms, 1), "میلی‌ثانیه")
print("هزار commit در حالت WAL   :", round(wal_ms, 1), "میلی‌ثانیه")
print("نسبت                      :", round(one_by_one_ms / wal_ms, 1), "برابر")
```

```text-approx
هزار commit در حالت پیش‌فرض: 3131.8 میلی‌ثانیه
هزار commit در حالت WAL   : 694.6 میلی‌ثانیه
نسبت                      : 4.5 برابر
```

<div dir="rtl">

**همان هزار `commit`، چند برابر ارزان‌تر — ولی هنوز بیش از صد برابر گران‌تر از یک تراکنش.**

این ترتیب مهم است و اغلب برعکسش را می‌شنوی: **اول `commit`ها را کم کن، بعد سراغِ تنظیمات برو.** `WAL` یک بهبودِ واقعی است، ولی جای طراحیِ درست را نمی‌گیرد.

## ۵. یک نویسنده، و خطایی که از آن می‌آید

`SQLite` می‌گذارد هر تعداد اتصال هم‌زمان **بخوانند**، ولی در هر لحظه فقط **یکی** می‌تواند بنویسد. حالا عمداً این حالت را می‌سازیم: یک اتصال تراکنشِ نوشتن را باز نگه می‌دارد و اتصالِ دوم می‌خواهد بنویسد.

> 🔧 **اگر کار نکرد:** این همان خطایی است که در هر برنامهٔ واقعی با `SQLite` بالاخره یک بار می‌بینی:

</div>

```python
import sqlite3

other = sqlite3.connect(DB_PATH, timeout=0.5)

con.execute("BEGIN IMMEDIATE")
con.execute("INSERT INTO queue VALUES (?, ?)", (1, "از اتصال اول"))

try:
    other.execute("INSERT INTO queue VALUES (?, ?)", (2, "از اتصال دوم"))
    other.commit()
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
    other.rollback()

print("خواندن از اتصال دوم، همان لحظه:",
      other.execute("SELECT COUNT(*) FROM events").fetchone()[0])
```

```text
sqlite3.OperationalError
database is locked
خواندن از اتصال دوم، همان لحظه: 400000
```

<div dir="rtl">

**نوشتن رد شد، خواندن کار کرد.** این دقیقاً همان قانون است: یک نویسنده، هر تعداد خواننده.

و حالا رفعش، که چهار تکه دارد — **و تکهٔ اولش همان `other.rollback()` بالاست:**

</div>

```python
con.commit()
other.execute("INSERT INTO queue VALUES (?, ?)", (2, "از اتصال دوم"))
other.commit()
print("سطرهای queue بعد از هر دو نوشتن:",
      int(q("SELECT COUNT(*) AS n FROM queue").iloc[0, 0]))
other.close()
```

```text
سطرهای queue بعد از هر دو نوشتن: 2
```

<div dir="rtl">

1. **بعد از خطا، تراکنشِ ناتمام را ببند.** اتصالِ دوم موقعِ تلاش برای نوشتن یک تراکنش باز کرد و آن تراکنش با شکستِ دستور بسته نشد. این تراکنش تا وقتی فقط باز است مزاحمِ کسی نیست، ولی اگر اتصالِ دوم در همان حال چیزی بخواند — مثلِ همان `SELECT COUNT(*)` بالا، اگر `rollback` را جا بیندازی — قفلِ خواندن را نگه می‌دارد و **حتی `commit` اتصالِ اول هم شکست می‌خورد**. یعنی خطا از قربانی به مقصر سرایت می‌کند.
1. **تراکنش را باز نگه ندار.** آن `BEGIN IMMEDIATE` تا لحظهٔ `commit` قفل را در دست داشت؛ هر ثانیه‌ای که یک تراکنش باز می‌ماند، ثانیه‌ای است که بقیه منتظرند.
2. **به اتصالت مهلت بده.** ما عمداً `timeout=0.5` گذاشتیم تا خطا را ببینی؛ مقدارِ پیش‌فرض چند ثانیه است و بیشترِ برخوردهای کوتاه را خودش حل می‌کند.
3. **در حالتِ `WAL` خواننده‌ها اصلاً منتظرِ نویسنده نمی‌مانند** — ولی دو نویسندهٔ هم‌زمان همچنان همان خطا را می‌گیرند.

**و مهم‌تر از هر چهار: این محدودیت رفع‌شدنی نیست، فقط مدیریت‌شدنی است.** فصلِ ۷ می‌گوید از کجا به بعد باید سراغِ چیزِ دیگری رفت.

## ۶. جدولِ قبل و بعدِ این فصل

</div>

| کاری که کردیم | قبل | بعد | ابزارِ تشخیص |
|---|---|---|---|
| هزار `commit` ← یک تراکنش | ~۳٬۱۰۰ میلی‌ثانیه | ~۵ میلی‌ثانیه | شمارشِ `commit`ها (نقشهٔ اجرا خالی است) |
| هزار `commit` ← هزار `commit` در `WAL` | ~۳٬۱۰۰ میلی‌ثانیه | ~۷۰۰ میلی‌ثانیه | `PRAGMA journal_mode` |

<div dir="rtl">

**ستونِ آخر عمداً «ابزارِ تشخیص» است، نه «نقشهٔ اجرا».** قاعدهٔ این ترم می‌گوید هیچ ادعای سرعتی بدونِ شاهد پذیرفته نیست؛ نمی‌گوید شاهد همیشه باید نقشهٔ اجرا باشد. **وقتی نقشه خالی است، وظیفه‌ات این است که بگویی به‌جایش به چه چیزی نگاه کردی** — نه اینکه ادعا را بدونِ شاهد بنویسی.

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** در `events` یک رویداد، در `queue` یک یادداشتِ ساختگی. **با کدام شمارشِ دوم سنجیدی؟** با سه‌تا: اختلافِ جمعِ `days` دقیقاً برابرِ `rowcount` شد (۱۹۲٬۱۶۹)؛ شمارشِ `events` بعد از `ROLLBACK` به ۴۰۰٬۰۰۰ برگشت؛ و هر دو راهِ نوشتن دقیقاً ۱٬۰۰۰ سطر ساختند. **چه کسی از قلم افتاد؟** در بخشِ ۵، **سطری که اتصالِ دوم می‌خواست بنویسد** — و اگر برنامه‌ات آن خطا را بگیرد و بی‌صدا رد شود، آن سطر برای همیشه گم می‌شود بدونِ اینکه کسی بفهمد.

> 🤖 **از دستیارت بپرس:** «چرا هزار `commit` جدا خیلی کندتر از یک تراکنش است؟» بعد این را بپرس: **«اگر برای سرعت، `commit` را از حلقه بیرون بیاورم، چه چیزی را از دست می‌دهم؟»** — جوابِ درست معامله را می‌گوید: اگر وسطِ کار برنامه بمیرد، در حالتِ اول سطرهای نوشته‌شده می‌مانند و در حالتِ دوم هیچ‌کدام نمی‌مانند. اگر جواب گفت «هیچ چیزی از دست نمی‌دهی»، بپرس در صورتِ قطعِ برق چه می‌شود.

## واژه‌های تازهٔ این فصل

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| transaction | ترنزاکشن | تراکنش؛ چند تغییر که یا همه اتفاق می‌افتند یا هیچ‌کدام |
| atomicity | اتمیسیتی | اتمی بودن؛ همان «همه یا هیچ» |
| `rowcount` | رو‌کانت | تعدادِ سطرهایی که آخرین دستور واقعاً عوض کرد |
| journal | ژورنال | دفترِ ثبتِ تغییرها، که `ROLLBACK` را ممکن می‌کند |
| `WAL` | وال | حالتی که تغییرها را به یک فایلِ پیوسته اضافه می‌کند |
| lock | لاک | قفل؛ حقِ انحصاریِ نوشتن که در هر لحظه دستِ یک اتصال است |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا نقشهٔ اجرای یک `INSERT` خالی است؟

**تمرینِ اجباری:** آزمایشِ بخشِ ۳ را با سه اندازه تکرار کن: صد سطر، هزار سطر، و ده هزار سطر. **برای هر اندازه، هر دو زمان و تعدادِ سطرِ نوشته‌شده را چاپ کن.** حالا جوابِ سؤالِ اصلی را بنویس: **زمانِ راهِ «یک تراکنش» با ده برابر شدنِ سطرها چند برابر می‌شود، و زمانِ راهِ «هر سطر یک `commit`» چند برابر؟** جوابت باید بگوید کدام‌یک با تعدادِ سطر رشد می‌کند و کدام با تعدادِ `commit`.

**تمرینِ ستاره‌دار ⭐:** یک تراکنش باز کن، پنج سطر به `queue` اضافه کن، **بعد عمداً یک `INSERT` بزن که قیدِ `PRIMARY KEY` را بشکند** و خطا بگیری. حالا بدونِ `commit` و بدونِ `rollback` بشمار ببین چند سطر در جدول است، بعد `rollback` کن و دوباره بشمار. حالا سؤالِ سخت را جواب بده: **آیا آن خطا خودش تراکنش را برگرداند، یا پنج سطرِ قبلی تا لحظهٔ `rollback` سرِ جایشان بودند؟** جوابت باید بگوید چرا نمی‌شود به «خطا گرفتم پس چیزی نوشته نشد» اعتماد کرد.

## در فصل بعد

تا اینجا برای سریع کردنِ کارها یا چیزی به پایگاه داده اضافه کردیم — `index` — یا شکلِ نوشتن را عوض کردیم. فصلِ بعد **هیچ‌کدام** را نمی‌کند: همان داده، همان `index`ها، و فقط **متنِ پرسش** عوض می‌شود.

چهار بازنویسی را امتحان می‌کنیم و نتیجه‌ها با هم فرق دارند. **یکی پرسش را نزدیک به دو برابر سریع می‌کند و بعد، به‌محضِ ساختنِ یک `index`، تبدیل به کندترِ دو تا می‌شود؛ دو تا هیچ فرقی نمی‌کنند و همان را هم گزارش می‌کنیم؛ و یکی جوابِ غلط می‌دهد بدونِ اینکه خطایی بگیرد** — پرسشی که جوابش صفر است در حالی که جوابِ درست ۹٬۱۶۰ است.

</div>

---
Source: https://yaadestan.com/courses/avand/lessons/t5/s05-writes-and-transactions (یادستان, Yaadestan)
