Multi-master일때 같은 row에 쓰기 시도를 하면 어떻게 될까?

코드

version: '2.1'

services:
  db-1:
    image: docker.io/bitnami/mariadb-galera:latest
    ports:
      - 127.0.0.1:3306:3306
      - 127.0.0.1:4444:4444
      - 127.0.0.1:4567:4567
      - 127.0.0.1:4568:4568
    volumes:
      - ./data/db1:/bitnami/mariadb
    environment:
      - ALLOW_EMPTY_PASSWORD=yes
      - MARIADB_GALERA_CLUSTER_BOOTSTRAP=yes 
      - MARIADB_GALERA_CLUSTER_NAME=galera-test 
      - MARIADB_GALERA_MARIABACKUP_USER=my_mariabackup_user 
      - MARIADB_GALERA_MARIABACKUP_PASSWORD=my_mariabackup_password 
      - MARIADB_ROOT_PASSWORD=rootpassword
      - MARIADB_USER=esukmean 
      - MARIADB_PASSWORD=esukmean1234 
      - MARIADB_DATABASE=esukmeans 
      - MARIADB_REPLICATION_USER=esukmean-rep 
      - MARIADB_REPLICATION_PASSWORD=esukmeanrep
    healthcheck:
      test: ['CMD', '/opt/bitnami/scripts/mariadb-galera/healthcheck.sh']
      interval: 15s
      timeout: 5s
      retries: 6
  db-2:
    image: docker.io/bitnami/mariadb-galera:latest
    ports:
      - 127.0.0.1:23306:3306
      - 127.0.0.1:24444:4444
      - 127.0.0.1:24567:4567
      - 127.0.0.1:24568:4568
    volumes:
      - ./data/db2:/bitnami/mariadb
    environment:
      - MARIADB_GALERA_CLUSTER_NAME=galera-test
      - MARIADB_GALERA_CLUSTER_ADDRESS=gcomm://db-1:4567,0.0.0.0:4567
      - MARIADB_GALERA_MARIABACKUP_USER=my_mariabackup_user 
      - MARIADB_GALERA_MARIABACKUP_PASSWORD=my_mariabackup_password 
      - MARIADB_ROOT_PASSWORD=rootpassword
      - MARIADB_USER=esukmean 
      - MARIADB_PASSWORD=esukmean1234 
      - MARIADB_DATABASE=esukmeans 
      - MARIADB_REPLICATION_USER=esukmean-rep 
      - MARIADB_REPLICATION_PASSWORD=esukmeanrep
    healthcheck:
      test: ['CMD', '/opt/bitnami/scripts/mariadb-galera/healthcheck.sh']
      interval: 15s
      timeout: 5s
      retries: 6
  db-3:
    image: docker.io/bitnami/mariadb-galera:latest
    ports:
      - 127.0.0.1:33306:3306
      - 127.0.0.1:34444:4444
      - 127.0.0.1:34567:4567
      - 127.0.0.1:34568:4568
    volumes:
      - ./data/db3:/bitnami/mariadb
    environment:
      - MARIADB_GALERA_CLUSTER_NAME=galera-test
      - MARIADB_GALERA_CLUSTER_ADDRESS=gcomm://db-1:4567,0.0.0.0:4567
      - MARIADB_GALERA_MARIABACKUP_USER=my_mariabackup_user 
      - MARIADB_GALERA_MARIABACKUP_PASSWORD=my_mariabackup_password 
      - MARIADB_ROOT_PASSWORD=rootpassword
      - MARIADB_USER=esukmean 
      - MARIADB_PASSWORD=esukmean1234 
      - MARIADB_DATABASE=esukmeans 
      - MARIADB_REPLICATION_USER=esukmean-rep 
      - MARIADB_REPLICATION_PASSWORD=esukmeanrep
    healthcheck:
      test: ['CMD', '/opt/bitnami/scripts/mariadb-galera/healthcheck.sh']
      interval: 15s
      timeout: 5s
      retries: 6

import sys
import pymysql.cursors
import time
import random

if len(sys.argv) < 3:
	print('please specifiy port and row count')
	exit()

server_port = int(sys.argv[1])
row_count = int(sys.argv[2])

conn = pymysql.connect(
						host='localhost',
						user='root',
						password='rootpassword',
						database='esukmeans',
						port=server_port,
						cursorclass=pymysql.cursors.DictCursor
					)

with conn:
	with conn.cursor() as cursor:
		# 메인이면 데이터 초기화를 해줌
		if len(sys.argv) == 4:
			cursor.execute('truncate table `test`')
			conn.commit()
		else:
			time.sleep(0.5) #데이터 준비까지 기다려줌

		for _ in range(row_count):
			result = cursor.execute("INSERT INTO `test` (`value`) VALUES (100000)", ())
			conn.commit()
			

		for _ in range(5000):
			rowno = random.randint(1, row_count)
			random_num = random.randint(-10000, 10000)

			prepare = (random_num)
			print(server_port, prepare)

			try:
				cursor.execute("UPDATE `test` SET `value`=`value` + (%s)", prepare)
				time.sleep(0.001)
				cursor.execute("UPDATE `test` SET `value`=`value` - (%s)", prepare)
				time.sleep(0.03)
				conn.commit()
			except Exception as e:
				print(e)

결론(MariaDB)

Single node면 lock을 통해서 알아서 기다리는데 (lock 대기 시간은 별도로 설정으로 뺄 수 있음) multi-master 상황에서는 공통으로 락을 걸 수 있는 상황도 아니고... 해서 아예 dead-lock으로 걸고 관련된 트랜잭션을 모두 죽여버림. 이 경우, 트랜잭션을 했던 커서는 posioned 되었기에 새로 생성해야함

CockroachDB

Multi-master를 자랑하는 Cockroach DB에서는 결과가 다를까.. 해서 빠르게 서버 세팅을 해 봄

version: '2.1'

services:
  db-1:
    image: cockroachdb/cockroach
    command: start --insecure --join=db-1,db-2,db-3
    volumes:
      - ./data/db1:/cockroach/cockroach-data
    ports:
      - "127.0.0.1:26257:26257"
      - "127.0.0.1:33333:8080"
  db-2:
    image: cockroachdb/cockroach
    command: start --insecure --join=db-1
    volumes:
      - ./data/db2:/cockroach/cockroach-data
    ports:
      - "127.0.0.1:26258:26257"
  db-3:
    image: cockroachdb/cockroach
    command: start --insecure --join=db-1
    volumes:
      - ./data/db3:/cockroach/cockroach-data
    ports:
      - "127.0.0.1:26259:26257"

cockroachdb는 pgsql의 커스텀 빌드같은 물건. 근데 pgsql에서는 트랜잭션에 실패하면 커넥션 단위로 poison 처리 해버려서 ㅡㅡ. MariaDB 계열은 커서만 재생성 하면 되는데, pgsql에서는 커낵션 자체를 다시 초기화 해야함

결과로 보면.. 여기도 마찬가지로 같은 row에 접근하면 트랜잭션 abort가 발생함. 덤으로 커넥션 단위 poison으로 인해 커낵션 재생성을 해야하는 제약까지 받아야함

과반수에 의한 투표기능은 이런곳에선 의미없는것으로.. 서버가 죽었을때 내가 마스터인지, 아님 분리된건지 확인해주는 용도밖에 안됨.