Upgrade to Pro — share decks privately, control downloads, hide ads and more …

A Deep Understanding of Transaction Isolation in Postgresql

A Deep Understanding of Transaction Isolation in Postgresql

A Deep Understanding of Transaction Isolation in Postgresql

Julia Kartashova, Senior Developer, Exactpro

DEV Meetup, Kostroma
1 November 2019

Video (RU): https://youtu.be/63LnB6RbX1M

To learn more about Exactpro, visit our website https://exactpro.com/
EXTENT Conference website: https://extent.exactpro.com/
Follow us on
LinkedIn https://www.linkedin.com/company/exactpro-systems-llc
Twitter https://twitter.com/exactpro
Facebook https://www.facebook.com/exactpro/
Instagram https://www.instagram.com/exactpro/

Subscribe to Exactpro Vimeo channel https://vimeo.com/exactproSubscribe to Exactpro YouTube channel https://www.youtube.com/c/exactprosystems

5206c19df417b8876825b5561344c1a0?s=128

Exactpro
PRO

November 01, 2019
Tweet

Transcript

  1. Build Software to Test Software exactpro.com Deep understanding transaction isolation

    in PostgreSQL Date: November 01, 19 Author: Julia Kartashova
  2. 2 Build Software to Test Software exactpro.com • Транзакция —

    группа последовательных операций с БД, которая представляет собой логическую единицу работы с данными. • Транзакция может быть выполнена либо целиком и успешно, либо не выполнена вообще. • Транзакция должна удовлетворять ACID: A - атомарность C - согласованность I - изолированность D - долговечность
  3. 3 Build Software to Test Software exactpro.com Уровни изоляции потерянное

    обновление грязное чтение неповторяю- щееся чтение фантомное чтение другие аномалии Read Uncommitted — + + + + Read Committed — — + + + Repeatable Read — — — + + Serializable — — — — —
  4. 4 Build Software to Test Software exactpro.com Потерянное обновление id

    account_name account_balance 4531 HWALP3284556 240.35 TRANSACTION 1: UPDATE ACCOUNTS SET account_balance = account_balance - 100 WHERE id = 4531; accounts TRANSACTION 2: UPDATE ACCOUNTS SET account_balance = account_balance - 120 WHERE id = 4531;
  5. 5 Build Software to Test Software exactpro.com Потерянное обновление id

    account_name account_balance 4531 HWALP3284556 240.35 TRANSACTION 1: UPDATE ACCOUNTS SET account_balance = account_balance - 100 WHERE id = 4531; accounts TRANSACTION 2: UPDATE ACCOUNTS SET account_balance = account_balance - 120 WHERE id = 4531; • не допускается стандартом ни на каком уровне изоляции • но можно добиться с помощью переменной на уровне READ COMMITTED
  6. 6 Build Software to Test Software exactpro.com Грязное чтение id

    account_name account_balance 4531 HWALP3284556 240.35 TRANSACTION 1: UPDATE ACCOUNTS SET account_balance = account_balance - 100 WHERE id = 4531; accounts
  7. 7 Build Software to Test Software exactpro.com Грязное чтение id

    account_name account_balance 4531 HWALP3284556 240.35 TRANSACTION 1: UPDATE ACCOUNTS SET account_balance = account_balance - 100 WHERE id = 4531; accounts TRANSACTION 2: SELECT id, account_balance FROM ACCOUNTS WHERE ID = 4531; id account_balance 4531 140.35
  8. 8 Build Software to Test Software exactpro.com Грязное чтение id

    account_name account_balance 4531 HWALP3284556 240.35 TRANSACTION 1: UPDATE ACCOUNTS SET account_balance = account_balance - 100 WHERE id = 4531; ROLLBACK; accounts TRANSACTION 2: SELECT id, account_balance FROM ACCOUNTS WHERE ID = 4531; id account_balance 4531 140.35
  9. 9 Build Software to Test Software exactpro.com Неповторяющееся чтение id

    account_name account_balance 4531 HWALP3284556 240.35 TRANSACTION 1: SELECT id, account_balance FROM ACCOUNTS WHERE id = 4531; accounts id account_balance 4531 240.35
  10. 10 Build Software to Test Software exactpro.com Неповторяющееся чтение id

    account_name account_balance 4531 HWALP3284556 240.35 TRANSACTION 1: SELECT id, account_balance FROM ACCOUNTS WHERE id = 4531; accounts TRANSACTION 2: UPDATE ACCOUNTS SET account_balance = 280.10 WHERE id = 4531; COMMIT; id account_balance 4531 240.35
  11. 11 Build Software to Test Software exactpro.com Неповторяющееся чтение id

    account_name account_balance 4531 HWALP3284556 240.35 TRANSACTION 1: SELECT id, account_balance FROM ACCOUNTS WHERE id = 4531; accounts TRANSACTION 2: UPDATE ACCOUNTS SET account_balance = 280.10 WHERE id = 4531; COMMIT; id account_balance 4531 240.35 SELECT id, account_balance FROM ACCOUNTS WHERE id = 4531; id account_balance 4531 280.10
  12. 12 Build Software to Test Software exactpro.com Фантомное чтение id

    account_name account_balance 4531 HWALP3284556 240.35 TRANSACTION 1: SELECT id, account_balance FROM ACCOUNTS WHERE account_name LIKE ‘HW%’; accounts id account_balance 4531 240.35
  13. 13 Build Software to Test Software exactpro.com Фантомное чтение id

    account_name account_balance 4531 HWALP3284556 240.35 TRANSACTION 1: SELECT id, account_balance FROM ACCOUNTS WHERE account_name LIKE ‘HW%’; accounts TRANSACTION 2: INSERT INTO ACCOUNTS (account_name, account_balance) VALUES (‘HWNPZ8351092’, 0.00); COMMIT; id account_balance 4531 240.35
  14. 14 Build Software to Test Software exactpro.com Фантомное чтение id

    account_name account_balance 4531 HWALP3284556 240.35 TRANSACTION 1: SELECT id, account_balance FROM ACCOUNTS WHERE account_name LIKE ‘HW%’; accounts TRANSACTION 2: INSERT INTO ACCOUNTS (account_name, account_balance) VALUES (‘HWNPZ8351092’, 0.00); COMMIT; id account_balance 4531 240.35 SELECT id, account_balance FROM ACCOUNTS WHERE account_name LIKE ‘HW%’; id account_balance 4531 240.35 4538 0.00
  15. 15 Build Software to Test Software exactpro.com Уровни изоляции потерянное

    обновление грязное чтение неповторяю- щееся чтение фантомное чтение другие аномалии Read Uncommitted — + + + + Read Committed — — + + + Repeatable Read — — — + + Serializable — — — — —
  16. 16 Build Software to Test Software exactpro.com потерянное обновление грязное

    чтение неповторяю- щееся чтение фантомное чтение другие аномалии Read Uncommitted — — + + + Read Committed — — + + + Repeatable Read — — — — + Serializable — — — — — Уровни изоляции
  17. 17 Build Software to Test Software exactpro.com Заголовок версии строки

    • xmin - номер транзакции, выполнившей команду INSERT • xmax - номер транзакции, выполнившей DELETE SELECT xmin, xmax, field1 from mytable; • infomask - ряд битов, определяющих свойства данной версии ◦ xmin_committed ◦ xmin_aborted ◦ xmax_committed ◦ xmax_aborted • ctid - ссылка на следующую, более новую, версию той же строки
  18. 18 Build Software to Test Software exactpro.com Заголовок версии строки

  19. 19 Build Software to Test Software exactpro.com Снимки данных READ

    COMMITTED REPEATABLE READ SERIALIZABLE
  20. 20 Build Software to Test Software exactpro.com Снимок данных •

    xmax - номер еще не существующей в системе транзакции • xip - список активных транзакций на момент создания снимка • xmin - номер самой ранней транзакции из активных • в снимке должны быть видны изменения: xmin <= xid < xmax, кроме xip * Все становится немного сложнее, если транзакция должна видеть только часть своих изменений. Но нам пока это не нужно.
  21. 21 Build Software to Test Software exactpro.com снимок данных транзакция

    видна транзакция не видна транзакция не видна xid
  22. 22 Build Software to Test Software exactpro.com Горизонт событий •

    xmin (номер самой ранней из активных транзакций) определяет «горизонт событий» транзакции • самый старый xmin определяет “горизонт событий” на уровне БД - неактуальные версии строк в этой БД уже никогда не будут видны ни одной транзакции, и их можно очистить • незавершенная транзакция будет удерживать горизонт самим фактом своего существования, old_snapshot_threshold - максимальное время жизни снимка ошибка snapshot too old idle_in_transaction_session_timeout - максимальное время жизни бездействующей транзакции, мс
  23. 23 Build Software to Test Software exactpro.com #502 xid #503

    #504 #505 #506 xmin xmax
  24. 24 Build Software to Test Software exactpro.com Service 1 Service

    2 Service N DB Service Service ... Service ... Service ... Create user: name : Jane password: 456 Audit Service # name password 5463 Jane 456
  25. 25 Build Software to Test Software exactpro.com Service 1 Service

    2 Service N DB Service Service ... Service ... Service ... User created: Id: 5463 name : Jane Audit Service # action data 1863 C {“name”: “Jane”, “password: 456”, “id”: 5463} # name password 5463 Jane 456
  26. 26 Build Software to Test Software exactpro.com Service 1 Service

    2 Service N DB Service Service ... Service ... Service ... Audit Service # action data 1863 C {“name”: “Jane”, “password: 456”, “id”: 5463} # name password 5463 Jane 456
  27. 27 Build Software to Test Software exactpro.com Service 1 Service

    2 Service N DB Service 1 Service ... Service ... Service ... Audit Service # action data 1863 C {“name”: “Jane”, “password: 456”, “id”: 5463} # name password 5463 Jane 456 DB Service 2 DB Service N DB Service N
  28. 28 Build Software to Test Software exactpro.com Что может произойти

    id username password 5278 John 8324 5279 Mary 8325 users 1. Новый подписчик читает текущий список юзеров
  29. 29 Build Software to Test Software exactpro.com Что может произойти

    id username password 5278 John qwerty 5279 Mary Mary123 5280 Anna secret users 1. Новый подписчик читает текущий список юзеров. 2. В этот момент кто-то добавляет новую запись. Триггер на таблице users добавляет новую запись в audit. id txid action new_data old_data 7835 559 INSERT {“id”:5280,”name”:”Anna”,password:”secret”} audit
  30. 30 Build Software to Test Software exactpro.com Что может произойти

    id username password 5278 John qwerty 5279 Mary Mary123 5280 Anna secret users 1. Новый подписчик читает текущий список юзеров. 2. В этот момент кто-то добавляет новую запись. Триггер на таблице users добавляет новую запись в audit. 3. Аудит сервис получает новую запись и рассылает обновление всем заинтересованным подписчикам. id txid action new_data old_data 7835 559 INSERT {“id”:5280,”name”:”Anna”,password:”secret”} audit
  31. 31 Build Software to Test Software exactpro.com Что может произойти

    id username password 5278 John qwerty 5279 Mary Mary123 5280 Anna secret users 1. Новый подписчик читает текущий список юзеров. 2. В этот момент кто-то добавляет новую запись. Триггер на таблице users добавляет новую запись в audit. 3. Аудит сервис получает новую запись и рассылает обновление всем заинтересованным подписчикам. 4. Аудит сервис получает подписку из п.1. Ему нужно отправить обновление только новому подписчику, потому что все остальные его уже получили. id txid action new_data old_data 7835 559 INSERT {“id”:5280,”name”:”Anna”,password:”secret”} audit
  32. 32 Build Software to Test Software exactpro.com Что может произойти

    id username password 5278 John 8324 5279 Mary 8325 5280 Anna secret users 1. Какая-то транзакция добавляет новую запись в таблицу users и коммитит эти изменения. id txid action new_data old_data 7835 559 INSERT {“id”:5280,”name”:”Anna”,password:”secret”} audit
  33. 33 Build Software to Test Software exactpro.com Что может произойти

    id username password 5278 John 8324 5279 Mary 8325 5280 Anna secret users 1. Какая-то транзакция добавляет новую запись в таблицу users и коммитит эти изменения. 2. Новый подписчик читает текущий список юзеров и видит в нем Anna. Но аудит сервис может не успеть прочитать эти изменения... id txid action new_data old_data 7835 559 INSERT {“id”:5280,”name”:”Anna”,password:”secret”} audit
  34. 34 Build Software to Test Software exactpro.com Что может произойти

    id username password 5278 John 8324 5279 Mary 8325 5280 Anna secret users 1. Какая-то транзакция добавляет новую запись в таблицу users и коммитит эти изменения. 2. Новый подписчик читает текущий список юзеров и видит в нем Anna. Но аудит сервис может не успеть прочитать эти изменения... 3. В этом случае, когда аудит сервис по таймеру начнет проверять новые апдейты, ему нужно отправить информацию о Anna всем подписчикам, кроме нового. id txid action new_data old_data 7835 559 INSERT {“id”:5280,”name”:”Anna”,password:”secret”} audit
  35. 35 Build Software to Test Software exactpro.com txid_current() и другие

    полезные функции https://www.postgresql.org/docs/9.6/functions-info.html
  36. 36 Build Software to Test Software exactpro.com Что мы делаем

    • В таблице audit хранится id транзакции, которая делала изменение txid_current() • При подписке (получении снапшота) проверяем, какие транзакции сейчас запущены mydb=> select txid_current_snapshot(); txid_current_snapshot ------------------------- 49612:49622:49612,49618 (1 row) То есть в снапшоте подписчик не видит 1. все изменения, сделанные транзакциями после 49622 2. изменения транзакции 49622 (xmax) и запущенными 49612, 49618 (xip) • Аудит сервис также проверяет текущий снимок, но читает данные, сделанные до xmin • Он сохраняет это число - latestTxid, и в следующий раз читает обновления, сделанные транзакциями от latestTxid до нового xmin. • Обновления рассылаются всем подписчикам, которые не видели это транзакцию. • Если при получении новой подписки latestTxid больше, чем ее xmax/xip, досылаем недостающее.
  37. 37 Build Software to Test Software exactpro.com Спасибо!