SQL 쿼리가 atomic인지 확인하는 방법

SQL 쿼리가 atomic인지 확인하는 방법

두 가지 관련 질문:

• 삽입물은 아토믹입니까(아래 코드 참조)?
• 삽입이 원자성이 있는지 여부를 판별하려면 어떻게 해야 합니까(즉, 그러한 보증을 제공하는 사양은 어디서 찾을 수 있습니까?)

SQL에 대해 더 개선해야 할 사항이 있습니까?해당 데이터는 고객 ID와 고객별 주문 ID로 각각 바인드된 청구서가 있는 테이블입니다.즉, 청구서 ID와 태플(클라이언트 ID, 주문 ID)은 고유해야 합니다.

첫 번째 버전은 다음과 같습니다.

DROP TABLE IF EXISTS invoice;

CREATE TABLE invoice (
    id int(11) AUTO_INCREMENT,
    client_id int(11) NOT NULL,
    order_id int(11) NOT NULL,
    INDEX(id, client_id, order_id)
);

DELIMITER //

CREATE TRIGGER invoice_insert
BEFORE insert
ON invoice
FOR EACH ROW
BEGIN
    SET @max_order_id = (SELECT IFNULL(MAX(order_id), 0) FROM invoice WHERE client_id = NEW.client_id);
    SET NEW.order_id = @max_order_id + 1;
END//

DELIMITER ;


-- example use
INSERT INTO invoice (client_id) VALUES (1), (1), (2), (1), (2), (2), (2);
INSERT INTO invoice (client_id) VALUES (1), (1), (2), (1), (2), (2), (2);
SELECT * from invoice;

두 번째 버전은 다음과 같습니다.

DROP TABLE IF EXISTS invoice, client;

CREATE TABLE invoice (
    id int(11) AUTO_INCREMENT,
    client_id int(11) NOT NULL,
    order_id int(11) NOT NULL,
    INDEX(id, client_id, order_id)
);

CREATE TABLE client (
    id int(11) AUTO_INCREMENT,
    max_order_id int(11) NOT NULL DEFAULT '0',
    INDEX(id)
);

DELIMITER //

CREATE TRIGGER invoice_insert
BEFORE INSERT
ON invoice
FOR EACH ROW
BEGIN
    SET @max_order_id = (SELECT max_order_id FROM client where id = NEW.client_id);
    SET NEW.order_id = @max_order_id + 1;
    UPDATE client SET max_order_id = NEW.order_id WHERE id = NEW.client_id;
END//

DELIMITER ;


-- example use
INSERT INTO client () VALUES (), (), ();
INSERT INTO invoice (client_id) VALUES (1), (1), (2), (1), (2), (2), (2);
INSERT INTO invoice (client_id) VALUES (1), (1), (2), (1), (2), (2), (2);
SELECT * from invoice;
SELECT * from client;

두 번째 버전은 클라이언트별로 별도의 주문 ID 카운터를 유지하므로 중복 주문 ID가 생성될 위험 없이 엔트리를 삭제할 수 있다는 장점이 있습니다.

먼저 OrderId에서 얻을 수 있는 가치가 무엇인지 묻고 싶습니다.각 OrderId는 1개의 청구서에 고유하게 할당되도록 되어 있기 때문에 청구서 ID를 사용하는 것은 어떨까요?

둘째, 트리거가 트랜잭션 내에서 작동하더라도 두 방법 모두 안전하지 않습니다.예를 들어, "동시에" 두 개의 송장이 들어오는 경우 한 클라이언트의 최대 OrderId=27이 있다고 가정합니다.자동 인크리먼트에 의해 ID 53 및 54 가 「」라고 말해 트리거가 개시됩니다.

53의 트랜잭션은 트리거를 시작하고 SELECT를 실행하므로 OrderId 27을 가져옵니다.트리거의 다음 문으로 진행되기 전에 54의 트랜잭션이 트리거를 시작하고 동일한 트리거를 실행하므로 27도 얻을 수 있습니다.그러면 각 트랜잭션은 트리거를 완료하므로 OrderId가 중복됩니다.

이 동작에 대한 한 가지 완화 방법은 트리거가 업데이트된 값을 읽기 위한 선택을 실행하기 전에 먼저 업데이트 문을 실행하는 것입니다(따라서 배타적 잠금을 잡는 것).그래도 그건 끔찍해

(a) 상기와 같이 청구서 ID를 사용하는 것이 좋습니다.또, 어떠한 이유로도 그것이 불가능한 경우는, (b) ORDER 테이블의 자동 증분이 필요합니다.

언급URL : https://stackoverflow.com/questions/25471562/how-to-determine-if-a-sql-query-is-atomic