www dbtechnet org Basics of SQL Transactions Big

www. dbtechnet. org Basics of SQL Transactions “Big Picture” for understanding COMMIT and ROLLBACK of SQL transactions Files, Buffers, Listener, Service Threads, and Transactions Martti Laiho 2012 -04 -02

(Flat) SQL Transaction [BEGIN TRANSACTION] [SET TRANSACTION ISOLATION. . ] SELECT … INSERT … UPDATE … DELETE … COMMIT or ROLLBACK Database Transaction log

Overview of a Database Server Instance connections (sessions) transactions - SQL commands & results network main memory listener & service threads bufferpool(s) DBMS instance processes (threads) x startup log cache(s) disc DBMS software instance control files database control files tablespace files database files transaction logs trace & alert files

Database Instance Architectures DB 2 Instance control files directories Oracle Instance bufferpools database control files directories bufferpools database For every database the active transaction logs build a circular chain Oracle calls the chained active transaction logs as redo-logs SQL Server Instance A server Instance may include just one or multiple databases depending on the DBMS product. System databases master bufferpool database In the following we will focus only on the simple case of a single database The active database transaction logs are allocated as Virtual Log Files in a single file building a circular chain Martti Laiho 2012 -04 -09

Files and Caches Listener Applications (clients) listening for connection requests of clients . . . Control Cache Transaction control, etc Cache of before and after images of rows of active transactions (Bufferpool) LRU protocol for freeing pages Checkpoint data pages for rows Circular chain of Transaction Logs Martti Laiho 2012 -04 -02 Data Cache of data pages for fast data processing minimizing disk I/O Log Cache WAL write-ahead-logging protocol Service threads (agents) Data Files on disks building the tablespace for tables and indices Log Archive (for history)

Structures of Data Pages Control data including pointers, dirty bit, LSN, etc Page header PCTFREE percentage of originally free space on pages Record of the row Record rest of the row Slot directory Address of a row is RID (ROWID) File# : Page# : Slot# Every data file has an internal file# in the database and the file space is managed as a sequence of pages. Page# is the ordinal number of the page in the file. A typical page size nowadays is 4, 8, 16 or 32 KB. Martti Laiho 2012 -04 -06 Slot directory A row may continue on other pages or original record may contain only a link to the new address, preserving the original RID value of the row’s Address.

SQL Session: A client starts its SQL connection. . connect? Listener. . . Control Cache Service threads (agents) Data Cache (Bufferpool) Log Cache Data Files Log Archive Transaction Logs Martti Laiho 2012 -04 -02

. . gets a service thread connect? Listener. . . Control Cache Service threads (agents) Data Cache (Bufferpool) Log Cache Data Files Log Archive Transaction Logs Martti Laiho 2012 -04 -02

. . Client enters a SQL command to update a row (still on disk) This first SQL command starts a new transaction update T set. . where id=. . Listener. . . Control Cache Service threads (agents) Transaction #tr Data Cache (Bufferpool) Log Cache # begin Data Files The transaction gets transaction ID (#) and a begin_transaction record is written to the Log Cache Log Archive Transaction Logs Martti Laiho 2012 -04 -02

DBMS reads the page of the row to bufferpool update T set. . where id=. . Listener. . . Control Cache Service threads (agents) Transaction #tr . . . Data Cache (Bufferpool) Log Cache # begin Data Files. . Transaction Logs Martti Laiho 2012 -04 -02 Log Archive

The row is updated and the updated page is marked with the Dirty Bit update T set. . where id=. . Listener. . . Control Cache Transaction #tr xxx dirty bit Service threads (agents) Data Cache (Bufferpool) Log Cache # begin Data Files Log Archive Transaction Logs Martti Laiho 2012 -04 -02

A log record of the before and after image of the row is written to log cache update T set. . where id=. . Listener. . . Control Cache Transaction #tr xxx dirty bit Service threads (agents) Data Cache (Bufferpool) Log Cache # begin xxx # #tr & before image & after image Data Files Log Archive Transaction Logs Martti Laiho 2012 -04 -02

On commit the log records of the transaction are written to the transaction log COMMIT Listener. . . Control Cache Transaction #tr xxx dirty bit Service threads (agents) Data Cache (Bufferpool) Log Cache # begin xxx # Data Files Log Archive # begin # xxx # commit Transaction Logs Martti Laiho 2012 -04 -02

. . but in case of ROLLBACK the before images are returned to original addresses ROLLBACK Listener. . . Control Cache Transaction #tr dirty bit Service threads (agents) Data Cache (Bufferpool) Log Cache # begin xxx # Data Files Log Archive # begin # xxx # rollback Transaction Logs Martti Laiho 2012 -04 -03

. . Later the client closes its SQL connection disconnect Listener. . . Control Cache xxx dirty bit Service threads (agents) Data Cache (Bufferpool) Log Cache Data Files Log Archive # begin # xxx # commit Transaction Logs Martti Laiho 2012 -04 -02

. . Some other pages may also get updated by SQL sessions of clients Listener. . . Control Cache #tr(s) . . . xxx Service threads (agents) X xx Data Cache (Bufferpool) Log Cache # # X xx Data Files Log Archive # begin # xxx # commit Transaction Logs Martti Laiho 2012 -04 -02 …

From time to time a checkpoint stops the services. . Listener Control Cache #tr(s) . . . Service threads (agents) Checkpoint. . . xxx Data Cache (Bufferpool) Log Cache # # X xx write-ahead-logging (WAL) Data Files 1. # begin # Log Archive xxx # commit Transaction Logs Martti Laiho 2012 -04 -07 …

and all dirty pages are written to data files clearing the dirty bits Listener Control Cache #tr(s) . . . Service threads (agents) Checkpoint. . . xxx Data Cache (Bufferpool) Log Cache 2. Data Files xxx xxx # begin # # commit X xx Transaction Logs Martti Laiho 2012 -04 -07 Log Archive …

and writes checkpoint record Listener. . . Control Cache #tr(s) Service threads (agents) Checkpoint. . . xxx Data Cache (Bufferpool) Log Cache Data Files Log Archive Checkpoint record - Chk Begin CBg ### CEn - #tr list - Chk End 3. xxx # begin # # # X xx # commit CBg ### CEn Transaction Logs Martti Laiho 2012 -04 -08 …

After the checkpoint the serving of clients can continue Listener. . . Control Cache #tr(s) . . . xxx Service threads (agents) xxx Data Cache (Bufferpool) Log Cache Data Files xxx xxx # begin # # # X xx # commit … CBg ### CEn Transaction Logs Martti Laiho 2012 -04 -02 Log Archive

Contents of full transaction log files are copied to archive. . Listener. . . Control Cache #tr(s) . . . xxx Service threads (agents) xxx Data Cache (Bufferpool) Log Cache Data Files xxx xxx # begin # # # X xx # commit … CBg ### CEn Transaction Logs Martti Laiho 2012 -04 -02 Log Archive # begin # xxx # commit …

. . and the space of the copied transaction log file can be reused Listener. . . Control Cache #tr(s) . . . xxx Service threads (agents) xxx Data Cache (Bufferpool) Log Cache Data Files xxx # # X xx CBg ### CEn Transaction Logs Martti Laiho 2012 -04 -02 Log Archive # begin # xxx # commit …

A logging test using SQL Server 2012 based on the original exercise by Kari Silpiö 21 Page header 10 11 10 22 11 21 2123 Slots Page ID 1: 77 . . 3210

Some Notes. . • • • The Least Recently Used (LRU) procedure is needed when DBMS needs to recover free space in the bufferpool(s). The pages which have been unused for the longest time and have dirty bit reset, will be cleared for new pages to be read from the data files. The active, circular transaction log files are the most important files of a database, since they contain data of the latest committed transactions! Dual logging (replicated) is recommended for these, and on dedicated disks. Based on the last checkpoint record and the circular transaction log a DBMS can recover the database after a soft crash automatically by roll-back recovery of failed and roll-forward recovery of the committed transactions to the level of the latest committed transasction before the soft crash. In case of hardware crash the database content need to be restored from the database backup and the applying roll-forward recovery of the transaction history from the archive and the latest circular logs, and finally rollback recovery of the latest failed transactions. These topics are covered in separate tutorials.