Как на высоконагруженном проекте правильно делать запросы к БД?

я так и делаю, но кэш баланса хочется тоже правильно обновлять:)

insert приход/расход - это по сути, логирование операций.

Его удобнее вести в отдельных независимых таблицах, а текущий баланс (и это вовсе не "кэш") делать update'ом, как это и задумывалось разработчиками СУБД.

Все, естественно, в рамках транзакции.

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

А как-то можно проверить, что транзакция работает? Если тип таблицы InnoDB, она точно запускается?:)

как вариант в одном клиенте открыть транзакицю, обратиться к таблице, а во втором клиенте тоже обратиться.

Но следует учитывать, что транзакции тоже есть на чтение, на запись.

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

А то суммирование по 500-5 000 записям - не очень идея. (особенно если старые записи могут удалятся, например через 3 года)

Обычно старые записи уходят в архив, а вместо их просто добавляется единственная с суммой ушедшего в архив.

Так действительно правильнее, когда баланс равен сумме транзакций.

Пересчитывание текущего баланса при каждом обращении просто идеологически неправильно.

Если с достоверностью баланса возникают проблемы, значит, либо неправильно спроектирована БД, либо есть ошибки в программной логике.

И решать проблему нужно поиском и исправлением ошибки, а не постоянным пересчитыванием недостоверных полей.

А зачем его пересчитывать постоянно ? Храните сумму баланса отдельно. Смысл в том, что эта сумма меняется только на основе данных транзакций, а не в результате плюс/минус от суммы в балансе.

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

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

В итоге имеем логи для бухгалтерии и поле для текущего просмотра баланса. Так же не будет проблем с архивом/восстановлением и тд логов.