While the column mention “tables”, it does not literally mean table locks, but rather the number of tables in which the transaction holds one or more InnoDB row locks. From the MySQL Reference Manual: TRX_TABLES_LOCKED: Number of InnoDB tables that the current SQL statement has row locks on. (Because these are row locks, not table […]
Archives for March 2021
What are the various types of locking used in the MySQL Server
MySQL uses locking at several levels of which table level locks and row level locks are the two most often encountered lock types. For each lock type, the lock held can in general be either a shared lock or an exclusive lock. Shared allows multiple processes to read the same data, but writes will block. […]
How MySQL Enterprise Backup (MEB) uses locking while making a backup
MySQL Enterprise Backup performs the backup in several steps: First the InnoDB data is copied. FLUSH TABLES WITH READ LOCK is executed. All other files are copied and other tasks requiring the lock are performed. See also below. The lock is released. If you only need to backup InnoDB tables, you can use one of […]
MySQL Enterprise Backup (MEB): Lock the Tables While Making the Backup?
MySQL 8.0.11 supports lock instance for backup but MySQL Enterprise Backup (MEB) not support that still uses FTWRL (FLUSH TABLES WITH READ LOCK). Support for avoiding FLUSH TABLES WITH READ LOCK for InnoDB tables was added in MySQL Enterprise Backup 8.0.16. Before 8.0.16, MySQL Enterprise Backup performs the backup in several steps: First the InnoDB […]
SQL script to find tables that are fragmented
This post intends to provide an SQL script to find tables that are fragmented (i.e. Data is much lower than High Water Mark) so that we can target those segments (tables) for recreation. pre-requisites Requires dba privileges as the script is to be run as the owner SYS or SYSTEM. It Will not work on […]
TRUNCATE TABLE not releasing space from tablespace
When we delete the rows from the table using the DELETE command, the extents that are allocated to the table will not be released and the table still holds them, whereas when using TRUNCATE with DROP STORAGE clause (which is the default), it will release the space back to the tablespace. But we see cases […]
How to reclaim entire space of an oracle database table with “Truncate Table” statement
Using the TRUNCATE statement provides a fast, efficient method for deleting all rows from a table or cluster. A TRUNCATE statement does not generate any undo information and it commits immediately. It is a DDL statement and cannot be rolled back. A TRUNCATE statement does not affect any structures associated with the table being truncated […]
Oracle SQL Script to Report Tablespace Free and Fragmentation
This script displays tablespace free space and fragmentation for each tablespace. It prints the total size, the amount of space available, and a summary of free space fragmentation in that tablespace. This can serve as an early warning system to prevent database space allocation crises. Note that no attempt is made to coalesce adjacent fragments. […]
Truncate Table Statement: REUSE STORAGE VS DROP STORAGE
Question: If we want to truncate any large table and reclaim space, which is the truncate command out of below 3 which can be used? TRUNCATE TABLE table_name; OR TRUNCATE TABLE table_name DROP STORAGE; OR TRUNCATE TABLE table_name REUSE STORAGE; REUSE STORAGE VS DROP STORAGE Here is the difference between drop and reuse storage: DROP […]