Database Systems - Transactions with SQL - SET TRANSACTION - COMMIT - ROLLBACK - SAVEPOINT скачать в хорошем качестве

Database Systems - Transactions with SQL - SET TRANSACTION - COMMIT - ROLLBACK - SAVEPOINT

Isolation levels This lesson is about transaction statements for MySQL using the InnoDB storage engine, which are very similar to other storage engines. SET TRANSACTION - sets the isolation level for subsequent transactions. Syntax is: SET [ GLOBAL | SESSION ] TRANSACTION ISOLATION LEVEL [ SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED ]; Keywords: ISOLATION LEVEL – the keyword that follow balances the isolation of concurrent transactions against performance SERIALIZABLE - provides full isolation with longer transaction duration. READ UNCOMMITTED - permits conflicts between concurrent transactions with shorter duration. SESSION - sets the isolation level for all transactions in the scope of a current user session. GLOBAL - sets the isolation level for all transactions submitted to the MySQL server for all subsequent sessions, ignoring existing sessions are not affected. If neither Session or Global is specified, then the isolation level applies only to the next transaction in the session. Transaction boundaries A transaction boundary is the first or last statement of a transaction. A transaction boundary is one of three statements: START TRANSACTION - starts new transaction. COMMIT - commits current transaction. ROLLBACK - rolls back current transaction. A single transaction is any set of statements that start with START TRANSATION and end with COMMIT or ROLLBACK. If auto commit is turned ON, then all individual statements are separate transactions because they commit or rollback immediately. SET autocommit = [ OFF | ON ]; COMMIT and ROLLBACK have keywords (optional): AND CHAIN - overrides the autocommit setting and starts a new transaction with the same isolation level, similarly to as if a START TRANSACTION were executed. RELEASE - ends the current session and then is disconnected from the server. COMMIT [ AND CHAIN ] [ RELEASE ]; ROLLBACK [ AND CHAIN ] [ RELEASE ]; Savepoints A savepoint saves a specific point in a transaction, and is temporary. When the transaction commits, the savepoint is released. Savepoints are managed by 3 statements and followed by an identifier: SAVEPOINT - saves internal transaction data and associates the data with the identifier. RELEASE SAVEPOINT - discards the identifier and saved data. ROLLBACK TO - resets transaction data to the savepoint values, restarts processing at the savepoint, and releases all subsequent savepoints. SAVEPOINT identifier; ROLLBACK TO identifier; RELEASE SAVEPOINT identifier; Checkpoints dirty block – a database block not yet saved on storage media, but has been saved in main memory.   checkpoint - helps with rapid recovery from system failures by saving dirty blocks and log records. It does this by suspending database processing, writing all unsaved logs and checkpoints to the log file, writing all dirty blocks to storage media, and resuming database processing. A fuzzy checkpoint saves dirty blocks while it continues processing to improve database availability or uptime. Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!