Бази данни - MySQL

Трансакции. Заключване на данните при трансакции.

4 Comments

Трансакция наричаме последователност от SQL заявки, които трябва да изпълняват условието: или всичките да бъдат изпълнени или нито една от тях да не бъде изпълнена.
Може да дадем класически пример с банковите трансакции. Първо създаваме таблица за клиенти, после таблица за сметки.

Ще слагаме само основни колони, тъй като за нашата цел други няма да са ни необходими.

create database if not exists transaction_test;
use transaction_test;

drop table if exists customer_accounts;

drop table if exists customers;
create table customers(
id int auto_increment primary key,
name varchar(255) not null,
address varchar(255)
);



create table if not exists customer_accounts(
id int  auto_increment primary key,
amount double not null,
currency varchar(10),
customer_id int not null,
constraint foreign key  (customer_id) references customers(id) on delete restrict on update cascade
);

INSERT INTO `transaction_test`.`customers` (`name`, `address`) VALUES ('Ivan Petrov Iordanov', 'Sofia, Krasno selo 1000');
INSERT INTO `transaction_test`.`customers` (`name`, `address`) VALUES ('Stoyan Pavlov Pavlov', 'Sofia, Liuylin 7, bl. 34');
INSERT INTO `transaction_test`.`customers` (`name`, `address`) VALUES ('Iliya Mladenov Mladenov', 'Sofia, Nadezhda 2, bl 33');

INSERT INTO `transaction_test`.`customer_accounts` (`amount`, `currency`, `customer_id`) VALUES ('5000', 'BGN', '1');
INSERT INTO `transaction_test`.`customer_accounts` (`amount`, `currency`, `customer_id`) VALUES ('10850', 'EUR', '1');
INSERT INTO `transaction_test`.`customer_accounts` (`amount`, `currency`, `customer_id`) VALUES ('1450000', 'BGN', '2');
INSERT INTO `transaction_test`.`customer_accounts` (`amount`, `currency`, `customer_id`) VALUES ('17850', 'EUR', '2');

Например, ако искаме да прехвърлим 50 лева от акаунт 1 в акаунт 2, то трябва да изпълним следните две заявки:

use transaction_test;
UPDATE customer_accounts 
SET amount = amount - 50 WHERE id = 1;

UPDATE customer_accounts
SET amount = amount + 50 
WHERE id = 2;

Какво обаче ще се случи, ако първата заявка се изпълни, но поради някаква причина втората не (например възникне грешка)?

Тук на помощ ни идват именно трансакциите – те гарантират, че ако някоя заявка не се изпълни, то данните ще бъдат възстановени в първоначалния им вид.

 

Синтаксис:

Групирането на заявки в трансакция се изпълнява изключително лесно:

Единствено трябва да оградим данните с BEGIN (започване на трансакция) и с COMMIT (край на трансакция).

Горният пример ще изглежда така:

begin;
use transaction_test;
UPDATE customer_accounts 
SET amount = amount - 50 WHERE id = 1;

UPDATE customer_accounts
SET amount = amount + 50 
WHERE id = 2;

commit;

Ако някоя от заявките пропадне, то се прави т.нар. ROLLBACK. За целта sql сървъра използва innodb log файл, в който се записват старите данни, преди изпълнението на всяка заявка.

Нека видим какво има в customer_accounts:

1

Изпълняваме долната заявка:

begin;
use transaction_test;
UPDATE customer_accounts 
SET amount = amount - 50 WHERE id = 1;

UPDATE customer_accounts
SET amount = amount + 50 
WHERE id = 2;

Съвсем умишлено няма да правим commit. Сега се връзваме към сървъра през друг клиент и проверяваме какво се случва:

2

Даваме commit; през MySQL Workbench-a и отново проверяваме през другия клиент:

3

 

Както виждате след commit вече промените са приложени върху таблиците и са видими за всички.

Трансакциите трябва да отговарят на условие за консистентно четене- тоест, последната сигурно отразена в базата информация. Това означава, че всеки SELECT чете данните, записани точно след последния COMMIT.

Когато един или повече редове се модифицират в рамките на незавършена транзакция, то те биват заключвани докато не се подаде команда COMMIT или ROLLBACK. Всяка друга заявка, опитваща се да достъпи такива заключени редове, моментално попада в т.нар. „спящ“ (idle-режим). Съществува определен timeout, след който заявката се анулира, ако съответните редове все още не са отключени  и транзакцията прави rollback. Заключването на връзките важи само за заявки от тип UPDATE, INSERT и DELETE.  По подразбиране то не е валидно за заявки от тип SELECT, но може и да бъде променено.

Даниел Джолев

4 thoughts on “Трансакции. Заключване на данните при трансакции.”

  1. Здравейте,
    със заявката по-горе искаме да прехвърлим 50 лв. от акаунт 1 в акаунт 2. Добре, но акаунт 1 е в лева, а акаунт 2 – в евро. След като заявката се изпълни в акаунт 2 постъпват 50 евро, а не 50 лв., бихте ли обяснили защо това е така ?
    Благодаря !

    Reply

    1. Здравейте,
      Съществен въпрос е. В реална ситуация ще трябва да се вземе предвид валутата. Ако тя е същата, няма проблем. Ако е различна, ще трябва да се направи превалутиране на прехвърляната сума, съгласно валутата на сметката, в която се прехвърля. Това е просто пример, в който местим пари от един акаунт в друг и наистина не се съобразяваме с валутата, което по същество е грешно. В реална ситуация ще има процедура, която ще се погрижи да обхване всички особености на трансфера на пари от една сметка в друга. Ще има и смислени условия кога да се подаде commit и кога да се подаде rollback.
      Поздрави!

      Reply

  2. решение на задачи от часа:
    1) имена, класове, тел на всички които тренират футбол

    SELECT students.name, students.class, students.phone
    FROM students JOIN student_sport
    ON students.id = student_sport.student_id
    JOIN sportGroups
    ON student_sport.sportGroup_id = sportGroups.id
    JOIN sports
    ON sportGroups.sport_id = sports.id
    WHERE sports.name = ‘Football’;

    2) изведете имената на всички треньори по волейбол

    SELECT coaches.name
    FROM coaches JOIN sportGroups
    ON (coaches.id = sportGroups.coach_id)
    JOIN sports
    ON (sportGroups.sport_id = sports.id)
    WHERE sports.name = ‘Volleyball’;

    3) изведете името на треньора и спорта който тренира с име Илиан Георгиев

    SELECT DISTINCT coaches.name, sports.name
    FROM coaches JOIN sportGroups
    ON coaches.id = sportGroups.coach_id
    JOIN sports
    ON sportGroups.sport_id = sports.id WHERE coaches.name = ‘Ilian Georgiev’;

    4) изведете сумите от платените през годините такси на учениците по месеци, но само за такси над 700 лева и треньор с егн 8504132420

    SELECT students.name, SUM(taxesPayments.paymentAmount) AS SumOfTaxes, taxesPayments.year
    FROM students JOIN taxesPayments
    ON students.id = taxesPayments.student_id
    GROUP BY student_id, year
    JOIN sportGroups
    ON taxesPayments.group_id = sportGroups.id
    JOIN coaches
    ON sportGroups.coach_id = coaches.id
    WHERE coaches.egn = ‘8504132420’
    HAVING SumOfTaxes >= 700;

    5) изведете броя на студентите във всяка една от групите

    SELECT sportGroups.location, sportGroups.dayOfWeek, sportGroups.hourOfTraining , COUNT(student_sport.student_id) AS Count_of_students_by_group
    FROM student_sport JOIN sportGroups
    ON student_sport.sportGroup_id = sportGroups.id
    GROUP BY sportGroup_id;

    Reply

  3. Десимир Красимиров Костадинов , 38 гр. , фак. № 121215122
    задачите от упражнението по БД от 27. 03. 2017 г.

    1 зад. Изведете име, клас и телефонен номер на всички , които тренират футбол
    Решения:
    начин 1 :
    select students.name,students.class, students.phone
    from students
    join sports
    on students.id in(
    select student_id
    from student_sports
    where sportGroup_id in (select id
    from sportgroups
    where sportgroups.sport_id=sports.id)
    )
    where sports.name=’Football’;
    начин 2 :
    select students.name,students.class, students.phone
    from students
    join student_sport
    on students.id=student_sport.student_id
    join sportgroups
    on student_sport.sportGroup_id=sportgroups.id
    join sports
    on sportgroups.sport_id=sports.id
    where sports.name=’Football’;

    2 зад. Изведете имената на всички треньори по волейбол
    Решение :
    select coaches.name as nameOfCoach
    from coaches
    join sportgroups
    on coaches.id=sportgroups.coach_id
    join sports
    on sportgroups.sport_id=sports.id
    where sports.name=’Volleyball’;

    3 зад. Изведете името на треньор и име на спорт за студента Илиян Иванов
    Решение :
    select distinct coaches.name as nameOfCoach, sports.name as nameOfSport
    from coaches join sportgroups
    on coaches.id = sportgroups.coach_id
    join sports
    on sportgroups.sport_id = sports.id
    where sportgroups.id in(
    select sportGroup_id
    from student_sport
    where student_id in(
    select id
    from students
    where students.name = “Iliyan Ivanov”));

    4 зад. Изведете сумите от платените през годините такси на студентите по месеци, но само за такси над 700 лева и треньор с егн 8203142724
    Решение :
    select students.name, sum(taxesPayments.paymentAmount) as sumOfTaxes, taxesPayments.year
    from students
    join taxesPayments
    on students.id = taxesPayments.student_id
    group by student_id, year
    join sportgroups
    on taxesPayments.group_id = sportGroups.id
    join coaches
    on sportGroups.coach_id = coaches.id
    where coaches.egn = ‘8203142724’
    having sumOfTaxes >= 700;

    5 зад. Изведете броя на студентите във всяка една от групите
    Решение:
    select student_sport.sportGroup_id, count(student_id) as numberOfStudents
    from students_sports
    group by sportgroup_id;

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *