Можно ли над методом рест-контроллера ставить аннотацию @Transactional?

#java #spring #транзакции #jta

Можно ли над методом рест-контроллера ставить аннотацию @Transactional?
Будут ли проблемы, если одновременно по этому URL одновременно будут пытаться получить
данные несколько клиентов?
    

Ответы

Ответ 1

Нельзя, однозначно и бесповоротно. Будут проблемы связанные с обработкой транзакций.



Здесь я укажу некоторые ссылки:


Слой использования @Transactional анотации..



  Что лучше всего с @Transactional, вы должны положить его, если у вас
  есть доступ к базе данных.
  
  См. «Понимание реализации декларативной транзакции Spring Framework»

 вы просто аннотируете свои классы аннотацией @Transactional, добавляете в свою конфигурацию
строку (``), а

  
  затем ожидаете, что вы поймете, как все это работает.



Какой слой использовать для транзакций и сессии Hibernate.



  Лучше всего использовать управление транзакциями Spring. Аннотации
  @Transactional для использования транзакций. На заводе-изготовителе
  используется LocalSessionFactoryBean. Все бобы управляются весной,
  поэтому у вас нет забот.



Spring Hibernate - различие между CrudRepository и SessionFactory.



  Просто вы можете найти описание этих аннотаций на сайте docs Spring.
  
  В ближайшее время, чтобы ответить на ваши вопросы, разница между ними
  заключается в том, что они используются для разных целей.

 @Transactional используется для демаркации кода, участвующего в транзакции. Он помещается
на классы и методы.

 @Repository используется для определения Spring-компонента, поддерживающего транзакции,
его также можно использовать в DI.

  
  Они могут использоваться как в одном классе.


Ответ 2

Можно. Никаких технических ограничений для этого нет. Но не нужно, так как это неправильно
с точки зрения проектирования архитектуры. Ни контроллеры, ни слой доступа к данным
не могут располагать необходимыми знаниями о взаимосвязях данных, в контексте которых
имеет смысл транзакции применять. Это прерогатива сервисного слоя, в котором и должна
располагаться вся бизнес-логика.

UPDATE: Так как правильность моего ответа ставят под сомнение, придётся его дополнить.

Во-первых, мне приходилось видеть проекты крупных и солидных компаний, в которых
на протяжении многих лет транзакции успешно используется именно в web-слое. Во-вторых,
первая же ссылка в Google по запросу "spring @transactional @controller" ведёт на большой
SO, где люди делятся тем же опытом. Наконец, не может быть более железного аргумента,
чем рабочий код. Поэтому я накидал простенький проект и залил его на GitHub - https://github.com/TheDeadOne/spring-transactional-controller-demo.


Ответ 3

Можно

Пост процессор, увидев аннотацию @Transactional вокруг аннотированного класса создаст
прокси, в котором будут происходить (очень примерно) две вещи


Выполняться beginTransaction()  перед аннотированным методом (или
перед каждым публичным методом аннотированного класса)
Выполняться .getTransaction().commit()  после аннотированного метода
(или после каждого публичного метода аннотированного класса)


С точки зрения многопоточности от заворачивания контроллера в прокси ничего не меняется.

Но не нужно

А вот с точки зрения архитектуры это плохая идея. Первая буква в слове SOLID:
The Single Responsibility Principle
Ответственность контроллера - получить request и попросить кого нибудь его обработать
и отправить ответ. Не надо вешать на него дополнительную функциональность.

Вложенные транзакции SQL сервер

#sql #qt #sql_server #odbc #транзакции

Здравствуйте.

Ситуация следующая. Есть SQL Server 2008, в программе используется драйвер QODBC3.
Соединение установлено, данные пишутся/читаются.

Проблема, видимо, в отсутствии понимания принципа работы с вложенными транзакциями.
Судя по документации к восьмому серверу, каждый новый BEGIN TRANSACTION  увеличивает
счётчик TRANCOUNT на единицу. Каждый COMMIT уменьшает его на 1. Транзакция завершается
и фиксируются изменения, когда счётчик станет равен 0. У меня же выходит, что первый
же commit завершает транзакцию вне зависимости от того, сколько было BEGIN TRANSACTION.
Пусть имеется база и запрос к ней:  

db = QSqlDatabase::addDatabase("QODBC3");
...
query = new QSqlQuery(db);
...


Выполняем:

db.transaction(); 
      query->exec("INSERT INTO TestOnly (Value) VALUES('1')");
      db.transaction();
            query->exec("INSERT INTO TestOnly (Value) VALUES('2')");
      db.commit(); 
      query->exec("INSERT INTO TestOnly (Value) VALUES('3')");
db.rollback(); 


В результате в таблице оказываются все 3 значения ('1','2','3'). И в SQL SMS видно,
что после первого же коммита пропадает единственная активная транзакция. Соответвственно
последующий RollBack ничего не делает.

В чём здесь проблема? Я совсем не правильно понял идею со счётчиком открытых транзакций,
или просто драйвер/Qt не поддерживают такой функционал? Или я упустил что-то где-то
в настройках (сервера/драйвера)?



С проверкой возвращаемых значений операций открытия/закрытия транзакций пример выглядит
страшнее, но ничего не поделать:

    bool ok;

    ok = db.transaction(); qDebug() << "Start transaction" << " result = " << ok
<< "; errorText: " << db.lastError().text();
    PrintTranCount(); //0

    query->exec("INSERT INTO TestOnly (Value) VALUES('1')");
    PrintTranCount(); //1

    ok = db.transaction(); qDebug() << "Start transaction" << " result = " << ok
<< "; errorText: " << db.lastError().text();
    PrintTranCount(); //2

    query->exec("INSERT INTO TestOnly (Value) VALUES('2')");
    PrintTranCount(); //3

    ok = db.commit(); qDebug() << "Commit transaction" << " result = " << ok << ";
errorText: " << db.lastError().text();
    PrintTranCount(); //4

    query->exec("INSERT INTO TestOnly (Value) VALUES('3')");
    PrintTranCount(); //5

    ok = db.rollback();  qDebug() << "Rollback transaction" << " result = " << ok
<< "; errorText: " << db.lastError().text();
    PrintTranCount(); //6


Результат:

Start transaction  result =  true ; errorText:  " "  
Transaction Count   0: 0  
Transaction Count   1: 1   
Start transaction  result =  true ; errorText:  " "   
Transaction Count   2: 1   
Transaction Count   3: 1   
Commit transaction  result =  true ; errorText:  " "  
Transaction Count   4: 0  
Transaction Count   5: 0   
Rollback transaction  result =  true ; errorText:  " "  
Transaction Count   6: 0   


Иными словами, вторая db.transaction() не инкриминирует счётчик совсем и транзакция
полностью завершается после первого COMMIT'а. Буду благодарен за любые подсказки по
этому поводу.

Вывод значения счётчика транзакций:

void PrintTranCount()
{
    static int Num = 0;
    query->exec("SELECT @@TRANCOUNT");query->first();
    qDebug() << "Transaction Count  "<< Num << ": " <value(0).toInt();
    Num++;
}

    

Ответы

Ответ 1

Краткий ответ

Вызов метода QSqlDatabase::transaction() не начинает транзакцию в базе, а лишь включает
режим "ручной режим фиксации". Для того чтобы начать явную транзакцию, нужно отослать
базе непосредственно команду "begin tran".

Пояснения

Проблема заключалась в том, что я принимал желаемое поведение драйвера за действительное
и поленился залезть в исходники. Спасибо @teran за то, что "ткнул носом" куда надо.

Описание функции bool QSqlDatabase::transaction() гласит:


  Begins a transaction on the database if the driver supports
  transactions. Returns true if the operation succeeded. Otherwise it
  returns false.


Из этого описания я сделал ложный вывод о том, что драйвер отправляет базе команду
открытия транзакции, что, мягко говоря, не соотносится с действительностью.

Из исходников:

bool QSqlDatabase::transaction()
{
    if (!d->driver->hasFeature(QSqlDriver::Transactions))
        return false;
    return d->driver->beginTransaction();
}


Функция "спрашивает" драйвер о поддержке транзакций, вызывая hasFeature(QSqlDriver::Transactions).
Если получает положительный ответ - вызывает метод драйвера beginTransaction().

bool QODBCDriver::beginTransaction()
{
    Q_D(QODBCDriver);
    //---------------Проверяем открыта ли база-----------------
    if (!isOpen()) 
    { 
        qWarning("QODBCDriver::beginTransaction: Database not open");
        return false;
    }
    //------Инициализация переменной-параметра значением,-----
    //------отключающим автоматический commit операций--------
    SQLUINTEGER ac(SQL_AUTOCOMMIT_OFF);  
    //-------Устанавливаем этот параметр и проверяем на ошибки-----------------
    SQLRETURN r  = SQLSetConnectAttr(d->hDbc,
                                      SQL_ATTR_AUTOCOMMIT,
                                      (SQLPOINTER)size_t(ac),
                                      sizeof(ac));
    if (r != SQL_SUCCESS) {
        setLastError(qMakeError(tr("Unable to disable autocommit"),
                     QSqlError::TransactionError, d));
        return false;
    }
    return true;
}


В функции beginTransaction(), в свою очередь, единственное производимое действие
- отключение автоматического подтверждения изменений в базе. Никаких "begin tran".

В результате, с отключённым автокоммитом база данных при выполнении каждой инструкции
начинает неявную транзакцию. Это (а не сама функция transaction(), как я думал) и приводит
к установке счётчика транзакций @TRANCOUNT в единицу. Нет никакой вложенности транзакций
в данном случае, она одна и завершается сразу после первого же COMMIT'а.

Функция commit() (как и rollback()) содержит в себе вызов QODBCDriver::endTrans(),
который вновь отключает режим неявных транзакций. Это отключение объясняет, почему
в моём примере в результирующей таблице была и третья строка (та, что перед ROLLBACK'ом).
COMMIT перед отправкой команды вставки этой строки завершил неявную транзакцию и отключил
ручной режим фиксации, так что INSERT был автоматически зафиксирован в базе, а rollback()
отработал вхолостую.


Ответ 2

По поводу того, как работают вложенные транзакции на mssql:

use tempdb
go
set nocount on;
SELECT [@@TRANCOUNT]=@@TRANCOUNT ,[XACT_STATE()]=XACT_STATE() ,[IMPLICIT_TRANSACTIONS]=CASE
WHEN @@OPTIONS & 2 = 2 THEN 1 ELSE 0 END
begin tran
SELECT [@@TRANCOUNT]=@@TRANCOUNT ,[XACT_STATE()]=XACT_STATE() ,[IMPLICIT_TRANSACTIONS]=CASE
WHEN @@OPTIONS & 2 = 2 THEN 1 ELSE 0 END
begin tran
SELECT [@@TRANCOUNT]=@@TRANCOUNT ,[XACT_STATE()]=XACT_STATE() ,[IMPLICIT_TRANSACTIONS]=CASE
WHEN @@OPTIONS & 2 = 2 THEN 1 ELSE 0 END
commit
SELECT [@@TRANCOUNT]=@@TRANCOUNT ,[XACT_STATE()]=XACT_STATE() ,[IMPLICIT_TRANSACTIONS]=CASE
WHEN @@OPTIONS & 2 = 2 THEN 1 ELSE 0 END
commit
SELECT [@@TRANCOUNT]=@@TRANCOUNT ,[XACT_STATE()]=XACT_STATE() ,[IMPLICIT_TRANSACTIONS]=CASE
WHEN @@OPTIONS & 2 = 2 THEN 1 ELSE 0 END

@@TRANCOUNT XACT_STATE() IMPLICIT_TRANSACTIONS
----------- ------------ ---------------------
0           0            0
1           1            0
2           1            0
1           1            0
0           0            0

Влияние кол-ва запросов в транзакции на производительность

#sql #база_данных #транзакции

Нужно ли отправлять изменения в БД одной транзакцией, или каждый INSERT, UPDATE,
DELETE лучше выполнять отдельной транзакцией? Что лучше при выполнении 10 несвязанных
запросов (не нужно откатывать предыдущий запрос при ошибке последующего), отправить
их одной транзакцией, или каждый по отдельности? Какой вариант лучше для производительности
приложения и субд?
    

Ответы

Ответ 1

В общем случае ни первый ни второй вариант не являются оптимальными с точки зрения
производительности.

Если нужно удалить(вставить/поменять) действительно большое количество строк, в первом
случае мы получаем большие расходы на открытие, закрытие большого количества транзакций.

Во втором случае имеем слишком огромную транзакцию, которая тоже под конец работы
будет долго закрываться, да ещё и рискуем упасть, т.к. размеры транзакции ограничены(видел
такую ошибку всего на нескольких миллионах строк).

Как-то писал скрипт удаления "ненужных" данных. Сталкивался с обеими проблемами.
Скрипт запускался на выходных, и удалял несколько десятков миллионов строк. Т.е.
проблемы блокировок и прочего не волновали, в приоритете была скорость.
Чтобы наглядно продемонстрировать, написал небольшой скрипт. MS SQL Server.

Кратко что он делает:


T_TABLE - таблица с данными
T_RESULT - таблица с результатами эксперимента
P_INSERT_ROWS @Count - вставляет в таблицу T_TABLE @Count
строк
P_DALATE_ROWS @Count, @Pack - удаляет из таблицы T_TABLE все строки в транзакциях
по @Pack штук, и записывает затраченное время в таблицу T_RESULT.


Финальный скрипт запускает в цикле вставку 1 000 000 строк, и их удаления пачками
по 1, 10 .. 1 000 000 штук.
И затем выводит содержимое T_RESULT.

Скрипт:

USE tempdb;
GO
IF OBJECT_ID('P_INSERT_ROWS', 'P') IS NOT NULL DROP PROC P_INSERT_ROWS;
IF OBJECT_ID('P_DELETE_ROWS', 'P') IS NOT NULL DROP PROC P_DELETE_ROWS;
IF OBJECT_ID('T_TABLE', 'U') IS NOT NULL DROP TABLE T_TABLE;
IF OBJECT_ID('T_RESULT', 'U') IS NOT NULL DROP TABLE T_RESULT;
GO
CREATE TABLE T_TABLE (
  id INT IDENTITY(1,1),
  Number INT,
  String NVARCHAR(4000)
)
CREATE INDEX IN_T_TABLE_NUMBER ON T_TABLE(Number ASC)
CREATE TABLE T_RESULT (
  execute_time DATETIME,
  Cnt INT,
  Pack INT
)
GO
CREATE PROC P_INSERT_ROWS 
  @Count INT 
AS
;WITH CTE AS(
  SELECT 1 N UNION ALL SELECT N+1 FROM CTE WHERE N<@Count
)
INSERT T_TABLE
SELECT N, LEFT(REPLICATE(N, 100),4000)
FROM CTE
OPTION(MAXRECURSION 0)
GO
CREATE PROC P_DELETE_ROWS 
  @Count INT,
  @Pack INT
AS
DECLARE @TTT DATETIME = GETDATE();
DECLARE @NumberStart INT = 1
WHILE @NumberStart < @Count
BEGIN
  BEGIN TRAN;
    DELETE FROM T_TABLE WHERE Number BETWEEN @NumberStart AND @NumberStart + @Pack
- 1 OPTION(RECOMPILE);
  COMMIT TRAN;
  SET @NumberStart += @Pack;
END;
INSERT T_RESULT
SELECT GETDATE()-@TTT execute_time, @Count Cnt, @Pack Pack
GO
SET NOCOUNT ON;
DECLARE 
  @Count INT = 1000000,
  @Pack INT = 1
WHILE @Pack <= @Count
BEGIN
  EXEC P_INSERT_ROWS @Count
  EXEC P_DELETE_ROWS @Count, @Pack
  SET @Pack *= 10;
END
SELECT * FROM T_RESULT
GO


Содержимое T_RESULT:

execute_time    Cnt Pack
1900-01-01 00:15:05.183 1000000 1
1900-01-01 00:01:56.003 1000000 10
1900-01-01 00:00:32.200 1000000 100
1900-01-01 00:00:22.370 1000000 1000
1900-01-01 00:00:21.940 1000000 10000
1900-01-01 00:00:22.663 1000000 100000
1900-01-01 00:01:10.327 1000000 1000000


Как видим, лучший результат по времени выполнения имеем, когда удаляется в одной
транзакции пачками по 10000 штук, т.е. это намного эффективнее, чем удалять по одной
записи в транзакции, и эффективнее, чем удалить все строки в одной транзакции.
Эксперимент не строгий, запуски влияют друг на друга, но всё равно показательно.

UPD: Добавлю, что на практике, транзакции в реальной жизни не часто бывают большими.
Кроме запросов вида DELETE FROM T, пожалуй.:) В этом случае да, чаще всего если есть
возможность обернуть несколько действий в одну транзакцию - лучше обернуть.


Ответ 2

Я полагаю, что такая постановка вопроса изначально неверна. Транзакция по сути своей
это атомарная операция с точки зрения бизнес-логики, а не с точки зрения БД. Именно так. 

Простейший классический пример с дебетом и кредитом счетов. Дебет и кредит разных
счетов должны производиться в пределах одной транзакции, безотносительно вопросов к
производительности. То есть списание денег с одного счета и зачисление той же суммы
на другой счет - это одна транзакция (одна атомарная операция с точки зрения бизнес-логики).
Если мы начнем эту атомарность разбивать на 2 операции апеллируя к производительности
- это путь в никуда и приведет к нарушению целостности данных (как в примере с дебетом/кредитом:
при падении системы, деньги с одного счета спишутся, а на другой не зачислятся).

Посему, я бы считал, что границы транзакций должны определяться бизнес-логикой приложения,
но никак не исходя из других соображений. 

Другой вопрос, что если логика позволяет границы транзакций проводить и так и эдак,
то совершенно очевидно, что чем короче транзакция, тем она более производительна. Механизм
реализации транзакций предполагает в классическом варианте, блокировку таблиц на уровне
записей затрагиваемых транзакцией. Чем длиннее транзакция - тем больше записей блокируется.
Чем больше записей блокируется, тем ниже производительность.


Ответ 3

самый главный скажу что надо каждый раз отдельно открывать транзакцию потому что
транзакция определена 1 атомарная операция.
Если ты будешь использовать за нескольких изменение за 1 транзакцию то у тебя будет
в базе проблемы.


может создаться 'Dead Lock' ситуация.
нагрузка будет при открытом состоянии транзакция.
кто соединяется в этот базу и исполняет какие-то изменения , изменение остается неподтвержденной.
а это что означает будет проблема не сохраненная информация. Потому что когда ты будешь
это подтверждать мы не знаем.


я хочу отметить еще один момент транзакция определена одному реальному атомарные
операцию.это может состоять много операций за одним атомарным процессом. (можно использовать
' Insert' и 'Update' одном (атомарном) транзакция Ну это уже твой операция то есть
что ты делаешь в предметной области).

Если будет мини-проект и только ты будешь этом использоваться тогда нет проблемы.
Думать о дальнейшем!

Гранулированый select c последующим update

#mysql #sql #innodb #транзакции

Есть ли способы выполнения селекта с последующим апдейтом на выбранном наборе таким
образом, чтобы между двумя запросами не выполнились запросы из других соединений, при
этом не блокируя всю таблицу целиком?

Задача банальна - выбрать N записей, после чего все их обновить(несколько полей)
так, чтобы между выборкой и обновлением те же самые записи не были выбраны параллельно
в других соединениях.

Такие блокировки, как select for update не подходят, т.к они блокируют записи с индексом
только если задано условие на "пространство" индексированного поля.

Есть ли какие-либо адекаватные методы решения этой задачи?

UPDATE:

Запросы выглядят следующим образом(все они - в триггерах):

START TRANSACTION ;

        DROP TABLE IF EXISTS TempTable;
        CREATE TEMPORARY TABLE TempTable AS(
            SELECT units.id AS id, units.authkey AS authkey
            FROM availablePublicUnitsView units
                LEFT JOIN media_info
                    ON units.id=media_info.unit_id AND media_info.media_id=mediaId
            WHERE media_info.media_id IS NULL
            LIMIT unitsCount
            #FOR UPDATE
        );

        UPDATE units SET reserved=true, last_usage_time=NOW(), reservation_hash=reservationHash
        WHERE id IN (SELECT id from TempTable) AND reserved=false;

        SELECT * FROM TempTable;

COMMIT ;


В SQL`е я не эксперт, так что если что-то в корне делаю неверно, буду рад услышать,
что именно.
    

Ответы

Ответ 1

В итоге после проведения нескольких нагрузочных тестов оптимальным оказался вариант,
не использующий временной таблицы(каждый раз пересоздается, занимая основное время),
но использующий блокировку для обновления.

Сначала все необходимые записи обновляются с установкой "ключа" выборки в виде хэша
SHA-256(алгоритм не принципиален), затем все записи с установленным хэшом выбираются,
но уже после завершения транзакции. Работает решение довольно быстро и с минимальными
блокировками.

Важно также отметить, что данные из вью availablePublicUnitsView выбираются, предварительно
сортируясь в случайном порядке(ORDER BY RAND), сводя блокировки почти к нулю. 

CREATE PROCEDURE select_and_reserve_units(
    IN unitsCount INT,
    IN mediaId VARCHAR(40),
    IN reservationHash BINARY(64))
BEGIN

START TRANSACTION ;

    UPDATE units SET reserved=true, last_usage_time=NOW(), reservation_hash=reservationHash
    WHERE id IN (

        SELECT units.id
        FROM availablePublicUnitsView units
            LEFT JOIN media_info
                ON units.id=media_info.unit_id AND media_info.media_id=mediaId
        WHERE media_info.media_id IS NULL    
        FOR UPDATE

    ) LIMIT unitsCount;

COMMIT ;

SELECT unit.id AS id, unit.authkey AS authkey FROM units
WHERE reservation_hash=reservationHash AND reserved=true;

END;//



  P.S видимо, пора переходить на PostgreSQL...


Ответ 2

Я так поняла, нужно запретить параллельное выполнение этих запросов над одними и
теми же записями?

Попробуйте изменить уровень изоляции. При этом используя SELECT FOR UPDATE.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

READ UNCOMMITTED - разрешает другим чтение данных, даже если транзакция не подтверждена.

Либо REPEATABLE READ - должна блокировать только изменяемые строки.

Можно ли над методом рест-контроллера ставить аннотацию @Transactional?

Можно ли над методом рест-контроллера ставить аннотацию @Transactional? Будут ли проблемы, если одновременно по этому URL одновременно будут пытаться получить данные несколько клиентов?

Ответ

Нельзя, однозначно и бесповоротно. Будут проблемы связанные с обработкой транзакций.

Здесь я укажу некоторые ссылки:
Слой использования @Transactional анотации.
Что лучше всего с @Transactional, вы должны положить его, если у вас есть доступ к базе данных. См. «Понимание реализации декларативной транзакции Spring Framework»
вы просто аннотируете свои классы аннотацией @Transactional, добавляете в свою конфигурацию строку (``), а затем ожидаете, что вы поймете, как все это работает.
Какой слой использовать для транзакций и сессии Hibernate
Лучше всего использовать управление транзакциями Spring. Аннотации @Transactional для использования транзакций. На заводе-изготовителе используется LocalSessionFactoryBean. Все бобы управляются весной, поэтому у вас нет забот.
Spring Hibernate - различие между CrudRepository и SessionFactory
Просто вы можете найти описание этих аннотаций на сайте docs Spring. В ближайшее время, чтобы ответить на ваши вопросы, разница между ними заключается в том, что они используются для разных целей.
@Transactional используется для демаркации кода, участвующего в транзакции. Он помещается на классы и методы.
@Repository используется для определения Spring-компонента, поддерживающего транзакции, его также можно использовать в DI. Они могут использоваться как в одном классе.

Вложенные транзакции SQL сервер

Здравствуйте.
Ситуация следующая. Есть SQL Server 2008, в программе используется драйвер QODBC3. Соединение установлено, данные пишутся/читаются.
Проблема, видимо, в отсутствии понимания принципа работы с вложенными транзакциями. Судя по документации к восьмому серверу, каждый новый BEGIN TRANSACTION увеличивает счётчик TRANCOUNT на единицу. Каждый COMMIT уменьшает его на 1. Транзакция завершается и фиксируются изменения, когда счётчик станет равен 0. У меня же выходит, что первый же commit завершает транзакцию вне зависимости от того, сколько было BEGIN TRANSACTION. Пусть имеется база и запрос к ней:
db = QSqlDatabase::addDatabase("QODBC3"); ... query = new QSqlQuery(db); ...
Выполняем:
db.transaction(); query->exec("INSERT INTO TestOnly (Value) VALUES('1')"); db.transaction(); query->exec("INSERT INTO TestOnly (Value) VALUES('2')"); db.commit(); query->exec("INSERT INTO TestOnly (Value) VALUES('3')"); db.rollback();
В результате в таблице оказываются все 3 значения ('1','2','3'). И в SQL SMS видно, что после первого же коммита пропадает единственная активная транзакция. Соответвственно последующий RollBack ничего не делает.
В чём здесь проблема? Я совсем не правильно понял идею со счётчиком открытых транзакций, или просто драйвер/Qt не поддерживают такой функционал? Или я упустил что-то где-то в настройках (сервера/драйвера)?

С проверкой возвращаемых значений операций открытия/закрытия транзакций пример выглядит страшнее, но ничего не поделать:
bool ok;
ok = db.transaction(); qDebug() << "Start transaction" << " result = " << ok << "; errorText: " << db.lastError().text(); PrintTranCount(); //0
query->exec("INSERT INTO TestOnly (Value) VALUES('1')"); PrintTranCount(); //1
ok = db.transaction(); qDebug() << "Start transaction" << " result = " << ok << "; errorText: " << db.lastError().text(); PrintTranCount(); //2
query->exec("INSERT INTO TestOnly (Value) VALUES('2')"); PrintTranCount(); //3
ok = db.commit(); qDebug() << "Commit transaction" << " result = " << ok << "; errorText: " << db.lastError().text(); PrintTranCount(); //4
query->exec("INSERT INTO TestOnly (Value) VALUES('3')"); PrintTranCount(); //5
ok = db.rollback(); qDebug() << "Rollback transaction" << " result = " << ok << "; errorText: " << db.lastError().text(); PrintTranCount(); //6
Результат:
Start transaction result = true ; errorText: " " Transaction Count 0: 0 Transaction Count 1: 1 Start transaction result = true ; errorText: " " Transaction Count 2: 1 Transaction Count 3: 1 Commit transaction result = true ; errorText: " " Transaction Count 4: 0 Transaction Count 5: 0 Rollback transaction result = true ; errorText: " " Transaction Count 6: 0
Иными словами, вторая db.transaction() не инкриминирует счётчик совсем и транзакция полностью завершается после первого COMMIT'а. Буду благодарен за любые подсказки по этому поводу.
Вывод значения счётчика транзакций:
void PrintTranCount() { static int Num = 0; query->exec("SELECT @@TRANCOUNT");query->first(); qDebug() << "Transaction Count "<< Num << ": " <value(0).toInt(); Num++; }

Ответ

Краткий ответ
Вызов метода QSqlDatabase::transaction() не начинает транзакцию в базе, а лишь включает режим "ручной режим фиксации". Для того чтобы начать явную транзакцию, нужно отослать базе непосредственно команду "begin tran".
Пояснения
Проблема заключалась в том, что я принимал желаемое поведение драйвера за действительное и поленился залезть в исходники. Спасибо @teran за то, что "ткнул носом" куда надо.
Описание функции bool QSqlDatabase::transaction() гласит:
Begins a transaction on the database if the driver supports transactions. Returns true if the operation succeeded. Otherwise it returns false.
Из этого описания я сделал ложный вывод о том, что драйвер отправляет базе команду открытия транзакции, что, мягко говоря, не соотносится с действительностью.
Из исходников:
bool QSqlDatabase::transaction() { if (!d->driver->hasFeature(QSqlDriver::Transactions)) return false; return d->driver->beginTransaction(); }
Функция "спрашивает" драйвер о поддержке транзакций, вызывая hasFeature(QSqlDriver::Transactions). Если получает положительный ответ - вызывает метод драйвера beginTransaction().
bool QODBCDriver::beginTransaction() { Q_D(QODBCDriver); //---------------Проверяем открыта ли база----------------- if (!isOpen()) { qWarning("QODBCDriver::beginTransaction: Database not open"); return false; } //------Инициализация переменной-параметра значением,----- //------отключающим автоматический commit операций-------- SQLUINTEGER ac(SQL_AUTOCOMMIT_OFF); //-------Устанавливаем этот параметр и проверяем на ошибки----------------- SQLRETURN r = SQLSetConnectAttr(d->hDbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)size_t(ac), sizeof(ac)); if (r != SQL_SUCCESS) { setLastError(qMakeError(tr("Unable to disable autocommit"), QSqlError::TransactionError, d)); return false; } return true; }
В функции beginTransaction(), в свою очередь, единственное производимое действие - отключение автоматического подтверждения изменений в базе. Никаких "begin tran".
В результате, с отключённым автокоммитом база данных при выполнении каждой инструкции начинает неявную транзакцию. Это (а не сама функция transaction(), как я думал) и приводит к установке счётчика транзакций @TRANCOUNT в единицу. Нет никакой вложенности транзакций в данном случае, она одна и завершается сразу после первого же COMMIT'а.
Функция commit() (как и rollback()) содержит в себе вызов QODBCDriver::endTrans(), который вновь отключает режим неявных транзакций. Это отключение объясняет, почему в моём примере в результирующей таблице была и третья строка (та, что перед ROLLBACK'ом). COMMIT перед отправкой команды вставки этой строки завершил неявную транзакцию и отключил ручной режим фиксации, так что INSERT был автоматически зафиксирован в базе, а rollback() отработал вхолостую.

Влияние кол-ва запросов в транзакции на производительность

Нужно ли отправлять изменения в БД одной транзакцией, или каждый INSERT, UPDATE, DELETE лучше выполнять отдельной транзакцией? Что лучше при выполнении 10 несвязанных запросов (не нужно откатывать предыдущий запрос при ошибке последующего), отправить их одной транзакцией, или каждый по отдельности? Какой вариант лучше для производительности приложения и субд?

Ответ

В общем случае ни первый ни второй вариант не являются оптимальными с точки зрения производительности.
Если нужно удалить(вставить/поменять) действительно большое количество строк, в первом случае мы получаем большие расходы на открытие, закрытие большого количества транзакций.
Во втором случае имеем слишком огромную транзакцию, которая тоже под конец работы будет долго закрываться, да ещё и рискуем упасть, т.к. размеры транзакции ограничены(видел такую ошибку всего на нескольких миллионах строк).
Как-то писал скрипт удаления "ненужных" данных. Сталкивался с обеими проблемами. Скрипт запускался на выходных, и удалял несколько десятков миллионов строк. Т.е. проблемы блокировок и прочего не волновали, в приоритете была скорость. Чтобы наглядно продемонстрировать, написал небольшой скрипт. MS SQL Server
Кратко что он делает:
T_TABLE - таблица с данными T_RESULT - таблица с результатами эксперимента P_INSERT_ROWS @Count - вставляет в таблицу T_TABLE @Count строк P_DALATE_ROWS @Count, @Pack - удаляет из таблицы T_TABLE все строки в транзакциях по @Pack штук, и записывает затраченное время в таблицу T_RESULT
Финальный скрипт запускает в цикле вставку 1 000 000 строк, и их удаления пачками по 1, 10 .. 1 000 000 штук. И затем выводит содержимое T_RESULT
Скрипт:
USE tempdb; GO IF OBJECT_ID('P_INSERT_ROWS', 'P') IS NOT NULL DROP PROC P_INSERT_ROWS; IF OBJECT_ID('P_DELETE_ROWS', 'P') IS NOT NULL DROP PROC P_DELETE_ROWS; IF OBJECT_ID('T_TABLE', 'U') IS NOT NULL DROP TABLE T_TABLE; IF OBJECT_ID('T_RESULT', 'U') IS NOT NULL DROP TABLE T_RESULT; GO CREATE TABLE T_TABLE ( id INT IDENTITY(1,1), Number INT, String NVARCHAR(4000) ) CREATE INDEX IN_T_TABLE_NUMBER ON T_TABLE(Number ASC) CREATE TABLE T_RESULT ( execute_time DATETIME, Cnt INT, Pack INT ) GO CREATE PROC P_INSERT_ROWS @Count INT AS ;WITH CTE AS( SELECT 1 N UNION ALL SELECT N+1 FROM CTE WHERE N<@Count ) INSERT T_TABLE SELECT N, LEFT(REPLICATE(N, 100),4000) FROM CTE OPTION(MAXRECURSION 0) GO CREATE PROC P_DELETE_ROWS @Count INT, @Pack INT AS DECLARE @TTT DATETIME = GETDATE(); DECLARE @NumberStart INT = 1 WHILE @NumberStart < @Count BEGIN BEGIN TRAN; DELETE FROM T_TABLE WHERE Number BETWEEN @NumberStart AND @NumberStart + @Pack - 1 OPTION(RECOMPILE); COMMIT TRAN; SET @NumberStart += @Pack; END; INSERT T_RESULT SELECT GETDATE()-@TTT execute_time, @Count Cnt, @Pack Pack GO SET NOCOUNT ON; DECLARE @Count INT = 1000000, @Pack INT = 1 WHILE @Pack <= @Count BEGIN EXEC P_INSERT_ROWS @Count EXEC P_DELETE_ROWS @Count, @Pack SET @Pack *= 10; END SELECT * FROM T_RESULT GO
Содержимое T_RESULT
execute_time Cnt Pack 1900-01-01 00:15:05.183 1000000 1 1900-01-01 00:01:56.003 1000000 10 1900-01-01 00:00:32.200 1000000 100 1900-01-01 00:00:22.370 1000000 1000 1900-01-01 00:00:21.940 1000000 10000 1900-01-01 00:00:22.663 1000000 100000 1900-01-01 00:01:10.327 1000000 1000000
Как видим, лучший результат по времени выполнения имеем, когда удаляется в одной транзакции пачками по 10000 штук, т.е. это намного эффективнее, чем удалять по одной записи в транзакции, и эффективнее, чем удалить все строки в одной транзакции. Эксперимент не строгий, запуски влияют друг на друга, но всё равно показательно.
UPD: Добавлю, что на практике, транзакции в реальной жизни не часто бывают большими. Кроме запросов вида DELETE FROM T, пожалуй.:) В этом случае да, чаще всего если есть возможность обернуть несколько действий в одну транзакцию - лучше обернуть.

Гранулированый select c последующим update

Есть ли способы выполнения селекта с последующим апдейтом на выбранном наборе таким образом, чтобы между двумя запросами не выполнились запросы из других соединений, при этом не блокируя всю таблицу целиком?
Задача банальна - выбрать N записей, после чего все их обновить(несколько полей) так, чтобы между выборкой и обновлением те же самые записи не были выбраны параллельно в других соединениях.
Такие блокировки, как select for update не подходят, т.к они блокируют записи с индексом только если задано условие на "пространство" индексированного поля.
Есть ли какие-либо адекаватные методы решения этой задачи?
UPDATE:
Запросы выглядят следующим образом(все они - в триггерах):
START TRANSACTION ;
DROP TABLE IF EXISTS TempTable; CREATE TEMPORARY TABLE TempTable AS( SELECT units.id AS id, units.authkey AS authkey FROM availablePublicUnitsView units LEFT JOIN media_info ON units.id=media_info.unit_id AND media_info.media_id=mediaId WHERE media_info.media_id IS NULL LIMIT unitsCount #FOR UPDATE );
UPDATE units SET reserved=true, last_usage_time=NOW(), reservation_hash=reservationHash WHERE id IN (SELECT id from TempTable) AND reserved=false;
SELECT * FROM TempTable;
COMMIT ;
В SQL`е я не эксперт, так что если что-то в корне делаю неверно, буду рад услышать, что именно.

Ответ

В итоге после проведения нескольких нагрузочных тестов оптимальным оказался вариант, не использующий временной таблицы(каждый раз пересоздается, занимая основное время), но использующий блокировку для обновления.
Сначала все необходимые записи обновляются с установкой "ключа" выборки в виде хэша SHA-256(алгоритм не принципиален), затем все записи с установленным хэшом выбираются, но уже после завершения транзакции. Работает решение довольно быстро и с минимальными блокировками.
Важно также отметить, что данные из вью availablePublicUnitsView выбираются, предварительно сортируясь в случайном порядке(ORDER BY RAND), сводя блокировки почти к нулю.
CREATE PROCEDURE select_and_reserve_units( IN unitsCount INT, IN mediaId VARCHAR(40), IN reservationHash BINARY(64)) BEGIN
START TRANSACTION ;
UPDATE units SET reserved=true, last_usage_time=NOW(), reservation_hash=reservationHash WHERE id IN (
SELECT units.id FROM availablePublicUnitsView units LEFT JOIN media_info ON units.id=media_info.unit_id AND media_info.media_id=mediaId WHERE media_info.media_id IS NULL FOR UPDATE
) LIMIT unitsCount;
COMMIT ;
SELECT unit.id AS id, unit.authkey AS authkey FROM units WHERE reservation_hash=reservationHash AND reserved=true;
END;//
P.S видимо, пора переходить на PostgreSQL...