Select For Update - Exclusive Lock

Giriş

Açıklaması şöyle

Exclusive lock, also referred to as a write lock, permits a single transaction to read and modify a record while preventing other transactions from accessing it.

An exclusive lock conflicts with other shared and exclusive lock.

If a row holds an exclusive lock, no other shared or exclusive lock can be acquired on the same row.

Select for update 3 şekilde kullanılabilir

1. Seçeneksiz : Satır üzerinde bloke olur

2. NOWAIT seçeneği : try lock gibidir. Satır üzerinde bloke olmaz

3. SKIP LOCKED seçeneği : Veriyi okurken eğer kilitli ise o satırı atlar

Açıklaması şöyle

NOWAIT: does not wait for the release of lock. If lock is held by another client and thus cannot be obtained, return immediately with message of lock conflict.

SKIP LOCKED: when reading data, skip rows whose row-level locks are held by other clients.

Örnek

Şöyle yaparız

/* session 1 acquires an xLock */
mysql> begin;

mysql> select * from user_tab where user_id=101 for update;
+---------+---------+------+-------------------+
| id      | user_id | age  | email             |
+---------+---------+------+-------------------+
| 1000076 |     101 |   10 | peter@hotmail.com |
+---------+---------+------+-------------------+

/* session 2 is blocked */
mysql> begin;

mysql> select * from user_tab where user_id=101 for share;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Distributed Lock

Giriş

lock_key alanı kilit ismini belirtiyor ve tekil

Örnek - Sadece Unique Key

Şöyle yaparız

CREATE TABLE lock (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  lock_key VARCHAR(256) NOT NULL,
  holder VARCHAR(256) NOT NULL,
  creation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_lock_key (lock_key)
);

Kullanmak için şöyle yaparız

// Acquire lock
INSERT INTO lock(lock_key, holder) VALUES ('project1_uid1', 'server1_ip1_tid1');

// Release lock
DELETE FROM `lock` WHERE lock_key = 'project1_uid1';

Örnek - Optimistic Lock

Şöyle yaparız

CREATE TABLE lock_table
(
 id INT AUTO INCREMENT COMMENT 'primary key',
 value VARCHAR(64) NULL COMMENT 'locked resource',
  version INT DEFAULT 0 NULL COMMENT 'version'
 CONSTRAINT lock_table_pk
  PRIMARY KEY (id)
);

UPDATE lock_table SET value = ..., version = #{version} + 1 
 WHERE id = #{id} AND version = #{version}；

Örnek - Sadece Unique Key + TLL

Şöyle yaparız. Burada TTL ile kilidi alan uygulama çökerse yani belli bir süre içinde kilidi bırakmazsa bir başka uygulama kilidi alabiliyor

CREATE TABLE lock (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  lock_key VARCHAR(256) NOT NULL,
  holder VARCHAR(256) NOT NULL,
  creation_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_lock_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_lock_key (lock_key)
);

Kullanmak için şöyle yaparız

// Aquire lock
UPDATE lock SET holder = 'server1_ip1_tid1', last_lock_time = ${now} 
WHERE 
  lock_key = 'project1_uid1' and 
  last_lock_time < ${now} - ${timeout};

// Release lock
UPDATE lock SET holder = '', last_lock_time = ${min_timestamp} 
WHERE 
  lock_key = 'project1_uid1' and 
  holder = 'server1_ip1_tid1';

Örnek - FOR UPDATE İle Pessimistic Lock

Şöyle yaparız

CREATE TABLE lock (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  lock_key VARCHAR(256) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_lock_key (lock_key)
);

Kullanmak için şöyle yaparız

// Acquire lock
BEGIN
SELECT * FROM demo.lock WHERE lock_key = 'project1_uid1' FOR UPDATE;

// Nonblocking Try lock
BEGIN
SELECT * FROM demo.lock WHERE lock_key = 'project1_uid1' FOR UPDATE NOWAIT;

// Relese Lock
COMMIT;

Testcontainers MySQLContainer Sınıfı

Giriş

Şu satırı dahil ederiz

import org.testcontainers.containers.MySQLContainer;

Maven

Şu satırı dahil ederiz

<dependency>
  <groupId>org.testcontainers</groupId>
  <artifactId>mysql</artifactId>
  <version>1.18.0</version>
  <scope>test</scope>
</dependency> 

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.30</version>
  <scope>test</scope>
</dependency>

Gradle

Şu satırı dahil ederiz

implementation platform('org.testcontainers:testcontainers-bom:1.16.3') //import bom
testImplementation ('org.testcontainers:junit-jupiter')  //no version specified
testImplementation('org.testcontainers:mysql') //no version specified
testImplementation 'mysql:mysql-connector-java:8.0.27'

Genel Kullanım

Bir ara benim ortamımdaki MySQLContainer sebepsiz yere çalışmamaya başladı. Daha sonra withTmpFs() ile kullanınca düzedi. Şöyle yaparız

MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0.32")
  .withUsername(...)
  .withPassword(...)
  .withLogConsumer(new Slf4jLogConsumer(LOGGER).withPrefix("Docker"))
  .withTmpFs(Map.of("/var/lib/mysql/", "rw",
                    "/tmp/", "rw"
));

Açıklaması şöyle

By default, MySQL stores database files in /var/lib/mysql.

DataSource İle Kullanım

1. com.mysql.cj.jdbc.MysqlDataSource İle Kullanımı

Örnek

En basit örnek şöyle. Bazı işleri kolaylaştıracak bir sınıfım olsun. Böylece hem docker image ismini, hem de veri tabanı ismini kolayca belirtirim

import org.testcontainers.containers.MySQLContainer;

public class CustomMySqlContainer extends MySQLContainer<CustomMySqlContainer> {
  private static final String DB_IMAGE = "mysql:8.0.11";

  public CustomMySqlContainer() {
    super(DB_IMAGE);
    withDatabaseName("ADV");
  }
}

Test içinde şöyle yaparız

import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;
import com.mysql.cj.jdbc.MysqlDataSource;
@Testcontainers
class FooTest {

  // will be shared between test methods
  @Container
  private static final CustomMySqlContainer container = new CustomMySqlContainer()
    .withInitScript("database/functions.sql");

  private static DataSource dataSource;

  @BeforeAll
  public static void setUp() {
    MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setUrl(container.getJdbcUrl());
    dataSource.setUser(container.getUsername());
    dataSource.setPassword(container.getPassword());
    FooTest.dataSource = dataSource;
  }

  @Test
  void truncateTable() throws SQLException {
    try (Connection connection = dataSource.getConnection()) {
      ...
    }
  }
}

2. Hikari İle Kullanımı

Örnek

Şöyle yaparız. Burada MySQLContainer sınıfının getJdbcUrl(), getUsername(), getPassword() gibi metodları kullanılıyor

@Testcontainers
class HikariDataSourceTest {

  // will be shared between test methods
  @Container
  MySQLContainer container = (MySQLContainer) new MySQLContainer("mysql:8.0.11")
  .withDatabaseName("ADV")
  .withInitScript("database/hikaridatasource.sql");
    
  @Test
  void localHikariDataSource() throws SQLException {
    HikariConfig hikariConfig = new HikariConfig();
    hikariConfig.setJdbcUrl(container.getJdbcUrl());
    hikariConfig.setUsername(container.getUsername());
    hikariConfig.setPassword(container.getPassword());
  
    try (HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig)) {
      try (Connection connection = hikariDataSource.getConnection()) {
        try (PreparedStatement preparedStatement = connection.prepareStatement("...")) {
          try (ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
              String string = resultSet.getString(1);
              System.out.println(string);
            }
          }
        }
      }
    }
  }
}

Metodlar

stop metodu

Container bir kere durdurulduktan sonra yeniden başlatılırsa yepyeni bir veri tabanı yaratılıyor. Dolayısıyla bu yeni URL ile JDBC connection almak gerekiyor. Açıklaması şöyle

Right now if you just stop() container, than it will be deleted immediately afterward.

Örnek

Şöyle yaparız

private static final MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0.11");

@Test
public void foo() throws SQLException {
  mysql.start();
  String jdbcUrl = mysql.getJdbcUrl();
  String username = mysql.getUsername();
  String password = mysql.getPassword();
  // Success 
  try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password)) {
    ...
  }
  
  mysql.stop();
  // Throws exception
  try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password)) {
    ...
  } catch (Exception exception) {
    ...
  }
  
  // Start again
  mysql.start();
  jdbcUrl = mysql.getJdbcUrl();
  username = mysql.getUsername();
  password = mysql.getPassword();
  // No exception
  try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password)) {
    ... 
  } catch (Exception exception) {
    ...
  }
}

Örnek

Ben bunu çalıştıramadım ama bu yazıya göre container'ı silmeden yani remove etmeden durdurma şöyle

@Testcontainers
public class SomeClass {
  @Test
  void stopAndStartContainer() {
    try (
      Network network = Network.newNetwork();
      GenericContainer<?> nginx = new GenericContainer("nginx:latest")
                        .withNetwork(network)
                        .withNetworkAliases("foo")
    ) {
      nginx.start();
      // starting UI
      // .....
      // doint some stuff with UI
      DockerClient client = DockerClientFactory.lazyClient();
      client.stopContainerCmd(nginx.getContainerId()).exec();
      // asserting reconnection pooling behaviour on UI
      client.startContainerCmd(nginx.getContainerId()).exec();
      // asserting successful reconnection behaviour on UI
    }
  }
}

withInitScript metodu

Örnek

Şöyle yaparız

private static final MySQLContainer mySQLContainer = new MySQLContainer<>("mysql:8.0.30")
  .withDatabaseName("testcontainer")
  .withUsername("user")
  .withPassword("pass")
  .withInitScript("init.sql");

test/java/resources/init.sql şöyledir

CREATE TABLE tests (
  id BIGINT AUTO_INCREMENT PRIMARY KEY
);

withLogConsumer metodu

Örnek

Şöyle yaparız

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.testcontainers.containers.output.Slf4jLogConsumer;

private static final Logger logger = LoggerFactory.getLogger(SimpleMySQLTest.class);


public static final String USERNAME = "mysql";
public static final String PASSWORD = "mysql";

private static final MySQLContainer<?> mysql = new MySQLContainer<>("mysql:5.7.34")
  .withUsername(USERNAME)
  .withPassword(PASSWORD)
  .withLogConsumer(new Slf4jLogConsumer(LOGGER).withPrefix("Docker"));

withUrlParam metodu

getJdbcUrl() çağrısı yapılınca döndürülecek URL String'ini ayarlamak içindir.

Örnek

Şöyle yaparız. MySQLContainer  root kullanıcı yaratmaya izin veriyor

MySQLContainer<?> container = new MySQLContainer<>("mysql:8.0.32")
  .withDatabaseName("mydb")
  .withUsername("root")
  .withUrlParam("user", "root")
  .withUrlParam("password", "test");

withReuse metodu

Örnek

Şöyle yaparız

public abstract class BaseIT {
  // ...
  @ServiceConnection
  private static final MySQLContainer mySQLContainer = new MySQLContainer("mysql:8.0");

  static {
    mySQLContainer.withUrlParam("serverTimezone", "UTC")
      .withReuse(true)
      .start();
  }
}

CREATE TABLE HASH partitioning

Giriş

Açıklaması şöyle. Sütun bir hash fonksiyonunda girdikten sonra dönen değer N kadar bölümlemeye ayrılır

HASH partitioning is when a partition is selected based on the value returned by a user-defined expression that operates on column values in rows to be inserted into the table. The function may consist of any expression valid in SQL that yields a non-negative integer value. An extension to this type, LINEAR HASH, is also available.

CREATE TABLE

Örnek

Şöyle yaparız

CREATE TABLE Sales (
  id INT NOT NULL,
  store_name VARCHAR(30),
  store_id INT
)
PARTITION BY HASH(store_id) PARTITIONS 4;

ALTER TABLE

Örnek

Şöyle yaparız

ALTER TABLE customers PARTITION BY HASH(store_id) PARTITIONS 4;

CREATE TABLE LIST partitioning

Giriş

Açıklaması şöyle.  Sütün hangi listeye girerse o bölümlemeye yazılır

LIST partitioning is similar to partitioning by RANGE, except that the partition is selected based on columns matching one of a set of discrete values.

Açıklaması şöyle. Yani sütunun unique olması gerekir

Remember that this partition requires the column to be a primary index or part of a compound primary index. 

Örnek

Şöyle yaparız

CREATE TABLE Table (val INT)
PARTITION BY LIST(val)(
  PARTITION myFirstPart VALUES IN (1,3,5),
  PARTITION MyPart VALUES IN (2,4,6),
  .........
);

Örnek

Şöyle yaparız

# create a compound primary index between the id and store id 
ALTER TABLE customers ADD UNIQUE (id, store_id);

ALTER TABLE customers
PARTITION BY LIST(store_id)(
  PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25),
  PARTITION p1 VALUES IN (26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50),
  PARTITION p2 VALUES IN (51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75),
  PARTITION p3 VALUES IN (76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101)
);

CREATE TABLE - RANGE partitioning

Giriş

Açıklaması şöyle

RANGE partitioning assigns rows to partitions based on column values falling within a given range.

CREATE TABLE veya ALTER TABLE ile kullanılabilir

CREATE TABLE

Örnek

Şöyle yaparız

CREATE TABLE Table (
  id INT NOT NULL,
  firstname VARCHAR(25) NOT NULL,
  joined DATE NOT NULL
)
PARTITION BY RANGE( id) (
  PARTITION p0 VALUES LESS THAN (100),
  PARTITION p1 VALUES LESS THAN (200),
  PARTITION p2 VALUES LESS THAN (300)
);

ALTER TABLE

Örnek

Şöyle yaparız

ALTER TABLE customers
PARTITION BY RANGE (id)
(
   PARTITION p0 VALUES LESS THAN (5000000),
   PARTITION p1 VALUES LESS THAN (10000000),
   PARTITION p2 VALUES LESS THAN (15000000),
   PARTITION pmax VALUES LESS THAN MAXVALUE
);
# query a single partition
SELECT COUNT(id) FROM customers PARTITION (pmax) 
WHERE registered_at > "2023-01-13 00:00:00";




Clustered vs Non-clustered indexes

Clustered Index

Primary Key anlamına gelir gibi düşünülebilir. Açıklaması şöyle.  

Clustered and non-clustered indexes

The primary key index is also a clustered index, and the non-primary key index is non-clustered. 

Non-Clustered Index

CREATE INDEX ile yaratılır

InnoDB - Storage Engine

Giriş

Açıklaması şöyle. MySQL 5.5'ten sonra varsayılan depolama motoru (storage engine) InnoDB oldu

MySQL can integrate with many different storage engines. Each storage engine has its own set of pros and cons for different use cases. In other words, storage engine can be considered as an interface, and can have different underlying implementations. For example, there are InnoDB, MyISAM, Memory, CSV, Archive, Merge, Blackhole.

InnoDB is certainly the most widely used. It is the default ever since MySQL 5.5 version.

Engine - InnoDB
Tablonun kullanacağı engine şöyle belirtilir.

CREATE TABLE myTable (...) ENGINE=InnoDB;

Şöyle yaparız.

CREATE TABLE my_table (...)
ENGINE=InnoDB AUTO_INCREMENT=4500000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Engine - MyISAM

MyISAM artık kullanılmıyor.

Eğer engine MyIsam ise tablo 3 dosyada tutulur.  tableName.MYI (indeksler için ) and tableName.MYD (veri için)

Eğer engine InnoDb ise tablo tek dosyada tutulur.

InnoDB Veriyi Nerede Saklar?

InnoDB veriyi hem bellekte hem de diskte saklar. Şeklen şöyle

1. Bellek

İki kısımdan oluşur

1. Buffer pool

2 Log buffer

1.1 Buffer pool

Açıklaması şöyle. Veriyi sayfalar (page) şeklinde bellekte saklar

Buffer pool is very important for InnoDB. MySQL usually is very fast in processing queries, and the reason is that the data is actually stored and served from memory (in most cases NOT from disk, contrary to what many people may think). This memory component is the buffer pool.

1.1.1 Sayfalar

- Her sayfa Doubly Linked List şeklinde birbirine bağlıdır

- Sayfada User Records bulunur. Bunlar Single Linked List şeklinde birbirine bağlıdır

- Yeni satır en sona eklenir

1.1.2 Infimum ve Supremum

Açıklaması şöyle. Yani bir sayfadaki en büyük ve en küçük primary key değerlerini saklarlar

The two fields represents the largest and smallest row in a page, respectively. In other words, the two form a min-max filter. By checking these two fields in O(1) time, InnoDB can decide whether the row to look for is stored in this particular page.

For example, say our primary key is numerical, and we have supremum = 1, infimum = 99 for a particular page. If we are trying to look for a row with primary key = 101, then clearly in O(1) time, InnoDB decides that it is not in this page, and will go to another page.

1.1.3 Page Directory

Açıklaması şöyle.

Now, if the target row is not in the page, InnoDB skips the page. However, if the row is actually in the page, does InnoDB still traverse the entire list? The answer is no, and the “other fields” again come handy to help.

Page directory

As the name suggests, it is like the “table of contents” of a book.

Page directory stores a pointer to a block of rows. Say we have row 1 to n stored in User Records, page directory will have pointers to row1, row7, row 13 …, with a block size of 6. This design is very similar to Skip List.

As you can probably imagine now, InnoDB first checks the page directory, and quickly determines which block to look into. Then utilizes the pointers to jump to the corresponding row in the singly linked list and start traversing from there. In this way, InnoDB avoids traversing the entire list, and needs to traverse a much smaller sublist.

The block we mentioned above is officially called Slot. Page directory has multiple slots, and each slot has a pointer to a row in the User Records.

Every time when a new row is inserted to User Records, InnoDB updates the Page Directory as well to make the two consistent with each other. InnoDB creates a slot for every 6 rows.

1.2 Change Buffer

Açıklaması şöyle.

Let’s say we updated some fields of the row id = 100. If a secondary index is built on one of the field, then the secondary index needs to be updated at the same time. However, if the page containing the secondary index is not in the Buffer Pool, then does InnoDB loads the page to Buffer Pool as well?

If the secondary index is not going to be used later, but every time eagerly gets loaded to Buffer Pool whenever a related field is updated, there will be a lot of random disk I/O operations, which will inevitably slow down InnoDB.

This is why Change Buffer is designed to “lazily” update secondary indexes.

When a secondary index needs to be updated, and the page containing it is not in the Buffer Pool, the update will be temporarily stored in Change Buffer. At a later time, if the page gets loaded into Buffer Pool (due to updates to primary index), InnoDB will merge the temporary changes in Change Buffer to pages in Buffer Pool.

There is a drawback to this Change Buffer design though. If the Change Buffer has accumulated a large amount of temporary changes, and say we want to merge it all at once with Buffer Pool, it may take hours to complete!! And during this merging, there will be a lot of disk I/O, which occupies CPU cycles, and affects the overall performance of MySQL.

So some tradeoff are further made. We earlier mentioned that merge is triggered when the page containing the secondary index gets loaded back to Buffer Pool. In InnoDB design, the merge can also be triggered by other events to avoid large merges that may take a few hours. These events include transactions, server shut-down, and sever restart.