In MySQL 8.0 there are two new features designed to support lock handling: NOWAIT and SKIP LOCKED. In this post, we’ll look at how MySQL 8.0 handles hot rows. Up until now, how have you handled locks that are part of an active transaction or are hot rows? It’s likely that you have the application attempt to access the data, and if there is a lock on the requested rows, you incur a timeout and have to retry the transaction. These two new features help you to implement sophisticated lock handling scenarios allowing you to handle timeouts better and improve the application’s performance.
To demonstrate I’ll use this product table.
mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.11 | +-----------+ 1 row in set (0.00 sec)
CREATE TABLE `product` ( `p_id` int(11) NOT NULL AUTO_INCREMENT, `p_name` varchar(255) DEFAULT NULL, `p_cost` decimal(19,4) NOT NULL, `p_availability` enum('YES','NO') …[Read more]