Tranzaksiyalar

-- Husanboyning hisobidan Sardorga 500 000 so'm o'tkazish
UPDATE hisoblar SET balans = balans - 500000 WHERE id = 1;
-- <-- SHU YERDA SERVER O'CHSA NIMA BO'LADI?
UPDATE hisoblar SET balans = balans + 500000 WHERE id = 2;

-- Tranzaksiya bilan
START TRANSACTION;

UPDATE hisoblar SET balans = balans - 500000 WHERE id = 1;
UPDATE hisoblar SET balans = balans + 500000 WHERE id = 2;

COMMIT;

START TRANSACTION;      -- yoki BEGIN;

-- ... so'rovlar ...

COMMIT;                 -- tasdiqlash
-- yoki
ROLLBACK;               -- bekor qilish

-- Amaliy misol: xavfsiz yangilash
START TRANSACTION;

UPDATE mahsulotlar SET narx = narx * 1.15 WHERE kategoriya_id = 3;

SELECT nomi, narx FROM mahsulotlar WHERE kategoriya_id = 3;   -- tekshiramiz

-- Natija to'g'ri bo'lsa:
COMMIT;

-- Xato bo'lsa:
ROLLBACK;

SELECT @@autocommit;      -- 1 = yoqilgan

-- O'chirish
SET autocommit = 0;

UPDATE talabalar SET ortacha_baho = 5.0;
ROLLBACK;                 -- endi bekor qilish ishlaydi

-- Qayta yoqish
SET autocommit = 1;

START TRANSACTION;

INSERT INTO mijozlar (ism, email) VALUES ('Husanboy', '[email protected]');
SAVEPOINT mijoz_qoshildi;

INSERT INTO buyurtmalar (mijoz_id, jami) VALUES (LAST_INSERT_ID(), 450000);
SAVEPOINT buyurtma_qoshildi;

INSERT INTO buyurtma_elementlari (buyurtma_id, mahsulot_id, soni, narx)
VALUES (LAST_INSERT_ID(), 999, 1, 100000);      -- xato: mahsulot yo'q

ROLLBACK TO buyurtma_qoshildi;      -- faqat oxirgi amalni bekor qilamiz

COMMIT;                              -- mijoz va buyurtma saqlanadi

-- Joriy daraja
SELECT @@transaction_isolation;

-- O'zgartirish
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Tranzaksiya A                    Tranzaksiya B
-------------------------------  -------------------------------
START TRANSACTION;
UPDATE hisoblar SET balans=0
    WHERE id=1;
                                 SELECT balans FROM hisoblar
                                     WHERE id=1;   -- 0 ko'radi!
ROLLBACK;                        -- lekin bu qiymat yo'q edi

Tranzaksiya A                    Tranzaksiya B
-------------------------------  -------------------------------
SELECT balans ... ;  -- 100000
                                 UPDATE ... SET balans=50000;
                                 COMMIT;
SELECT balans ... ;  -- 50000!  -- boshqa natija

Tranzaksiya A                    Tranzaksiya B
-------------------------------  -------------------------------
SELECT COUNT(*) ... ;  -- 10
                                 INSERT INTO ... ;
                                 COMMIT;
SELECT COUNT(*) ... ;  -- 11!

-- O'qish uchun bloklash (boshqalar o'qiy oladi, o'zgartira olmaydi)
START TRANSACTION;
SELECT * FROM mahsulotlar WHERE id = 1 LOCK IN SHARE MODE;
COMMIT;

-- Yozish uchun bloklash (boshqalar umuman tega olmaydi)
START TRANSACTION;
SELECT ombor FROM mahsulotlar WHERE id = 1 FOR UPDATE;

UPDATE mahsulotlar SET ombor = ombor - 1 WHERE id = 1;
COMMIT;

Tranzaksiya A                    Tranzaksiya B
-------------------------------  -------------------------------
UPDATE hisoblar WHERE id=1;      UPDATE hisoblar WHERE id=2;
    (1-qator bloklandi)              (2-qator bloklandi)
UPDATE hisoblar WHERE id=2;      UPDATE hisoblar WHERE id=1;
    (B ni kutadi)                    (A ni kutadi)
                DEADLOCK

-- Oxirgi deadlock haqida ma'lumot
SHOW ENGINE INNODB STATUS\G

-- Hozirgi bloklashlar
SELECT * FROM performance_schema.data_locks;

DELIMITER //

CREATE PROCEDURE buyurtma_yarat(
    IN p_mijoz_id INT UNSIGNED,
    IN p_mahsulot_id INT UNSIGNED,
    IN p_soni SMALLINT UNSIGNED
)
BEGIN
    DECLARE v_narx DECIMAL(12,2);
    DECLARE v_ombor INT;
    DECLARE v_buyurtma_id INT UNSIGNED;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Mahsulotni bloklaymiz
    SELECT narx, ombor INTO v_narx, v_ombor
    FROM mahsulotlar
    WHERE id = p_mahsulot_id
    FOR UPDATE;

    IF v_ombor < p_soni THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Omborda yetarli mahsulot yo''q';
    END IF;

    -- Buyurtma yaratamiz
    INSERT INTO buyurtmalar (mijoz_id, jami)
    VALUES (p_mijoz_id, v_narx * p_soni);

    SET v_buyurtma_id = LAST_INSERT_ID();

    -- Element qo'shamiz
    INSERT INTO buyurtma_elementlari (buyurtma_id, mahsulot_id, soni, narx)
    VALUES (v_buyurtma_id, p_mahsulot_id, p_soni, v_narx);

    -- Omborni kamaytiramiz
    UPDATE mahsulotlar SET ombor = ombor - p_soni WHERE id = p_mahsulot_id;

    COMMIT;
END //

DELIMITER ;

CALL buyurtma_yarat(1, 5, 2);

$pdo->beginTransaction();

try {
    $pdo->prepare('UPDATE hisoblar SET balans = balans - ? WHERE id = ?')
        ->execute([500000, 1]);

    $pdo->prepare('UPDATE hisoblar SET balans = balans + ? WHERE id = ?')
        ->execute([500000, 2]);

    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}