Freaks City

MySQL Lock Granularity and Types In MySQL, locking mechanisms are categorized by their scope into three primary types: global locks, table-level locks, and row-level locks. Global Locking Applying a global lock places the entire database instance into a read-only state. This is historically used for logical full-database backups to ensure consi ...

Backup Tools The mysqldump utility can be used on both the client and server sides. The SELECT INTO OUTFILE command is limited to writing data directly to the server side. Backup by Table a. Backing up a single table mysqldump -uusername -p database table1 >tableback.sql mysql> select * into outfile 'D:/someBookes/mysql/pracitce/mysql_ ...

Impact of Storage Engines on Transaction Handling In MyBatis development, developers often assume that every data modification requires an explicit sqlSession.commit() call to persist changes to the database. While this holds true for most production scenarios, the actual persistence behavior depends heavily on the underlying storage engine, sy ...

Server Layers and Storage Engines MySQL is split into three logical layers: Connection & Authentication – handles client hand-shake, privilege checks, SSL, thread pooling. SQL Layer – parses, optimizes, rewrites and executes statements. All built-in functions, views, triggers, stored procedures live here. The query cache was removed in 8.0 ...

MySQL System Architecture The MySQL server can be divided into four layers: Connection Layer – Includes client connections, connection pooling (authentication, caching), and thread pool management. Service Layer – Contains SQL interface, query parser, optimizer, caches/buffers, management tools, and built-in functions. It parses queries, creat ...

In enterprise Java development, the @Transactional annotation is widely used to manage database transactions. However, developers often encounter situations where the transaction does not roll back as expected. This article explores the fundamental concepts of transactions and lists the most common scenarios that cause Spring-managed transactio ...

InnoDB serves as MySQL's default storage engine and supports full transaction capabilities. Every operation runs within a transaction context; explicit BEGIN or START TRANSACTION statements start user-defined transactions, while implicit ones are created automatically for individual statements when not explicitly managed. MySQL defines four tra ...

Locks are essential for managing concurrent access to shared resources in databases. In MySQL, locks ensure data consistency and integrity when multiple transactions operate simultaneously. Based on granularity, MySQL supports three primary lock types: global, table-level, and row-level locks. Global Locks A global lock places the entire databa ...

When discussing database transactions, ACID properties (Atomicity, Consistency, Isolation, Durability) are fundamental. This exploration focuses on the 'I' – Isolation. Concurrent transaction execution can lead to issues like dirty reads, non-repeatable reads, and phantom reads. Transaction isolation levels are designed to mitigate these proble ...

Architectural Overview MySQL implements a four-layer architecture that handles operations from client connections down to physical file storage. Each layer addresses specific responsibilities, enabling MySQL to balance connectivity, query processing, data storage, and file management as distinct concerns. Connection Layer The connection layer h ...