Ms sql select from select: SQL Subquery | Nested query

Что такое SELECT FOR UPDATE в SQL (с примерами)?

SQL для разработчиков приложений

ORM — мощные инструменты, но они не идеальны. Изучите SQL, который вам нужен (и ничего из того, что вам не нужно).

Начало обучения

Реляционные базы данных отлично подходят для транзакционных рабочих нагрузок. Но все может запутаться, когда несколько транзакций начинают одновременно пытаться получить доступ к одним и тем же данным. К счастью, во многих базах данных SQL есть решение для этого: ВЫБЕРИТЕ ДЛЯ ОБНОВЛЕНИЯ .

Что такое ВЫБРАТЬ ДЛЯ ОБНОВЛЕНИЯ?

SELECT FOR UPDATE — это команда SQL, полезная в контексте транзакционных рабочих нагрузок. Он позволяет «блокировать» строки, возвращаемые запросом SELECT , до тех пор, пока вся транзакция, частью которой является запрос, не будет зафиксирована. Другие транзакции, пытающиеся получить доступ к этим строкам, помещаются в очередь ожидания на основе времени и выполняются в хронологическом порядке после завершения первой транзакции.

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

Другими словами: SELECT FOR UPDATE делает обработку спорных транзакций более плавной (что обычно также означает, что они обрабатываются быстрее и эффективнее).

Когда использовать SELECT FOR UPDATE

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

Однако разные «разновидности» SQL обрабатывают SELECT FOR UPDATE несколько по-разному, а некоторые вообще не поддерживают его. Например, MySQL поддерживает SELECT FOR UPDATE, а SQLite — нет.

Часто причина этого связана с тем, как различные системы обрабатывают транзакции в более широком смысле. Например, SQLite не нужно SELECT FOR UPDATE , так как инициирование транзакции блокирует всю базу данных. Это позволяет базам данных SQLite предлагать сериализуемую изоляцию транзакций, самый высокий уровень изоляции, для железной согласованности. Однако блокировка всей базы данных во время транзакции, которая может повлиять только на несколько строк, имеет очевидные отрицательные последствия для производительности, особенно при масштабировании.

Однако выбирать между производительностью и стабильностью не обязательно! CockroachDB, например, предлагает сериализуемую изоляцию, но это не не должен блокировать всю базу данных в начале транзакции, чтобы это произошло. SELECT FOR UPDATE можно использовать для максимизации производительности базы данных в случае параллельных транзакций, работающих с одними и теми же строками, и конечным результатом (в случае CockroachDB) по-прежнему является база данных с сериализуемой изоляцией.

[ блог ]

Как использовать ADD CONSTRAINT в SQL (с примерами)

Читать сообщение →

Различные базы данных SQL обеспечивают изоляцию транзакций, и, таким образом, SELECT FOR UPDATE , иначе, поэтому важно ознакомиться с параметрами и значениями по умолчанию для используемой вами системы.

Пример: SELECT FOR UPDATE в действии

Давайте посмотрим, как работает SELECT FOR UPDATE . Здесь мы будем использовать синтаксис и параметры CockroachDB, но синтаксис для других баз данных SQL, поддерживающих SELECT FOR UPDATE , будет аналогичным.

Представьте, что мы работаем с базой данных, которая включает в себя следующую таблицу кв :

Полная транзакция, использующая SELECT FOR UPDATE для этой таблицы, может выглядеть следующим образом:

 BEGIN;
SELECT * FROM kv WHERE k = 1 FOR UPDATE;
ОБНОВЛЕНИЕ kv SET v = v + 5, ГДЕ k = 1;
СОВЕРШИТЬ;
 

Построчная работа с оператором выше:

• Первая строка BEGIN инициирует транзакцию.
• Вторая строка, оператор SELECT , определяет затронутые строки и блокирует их. В данном случае это одна строка: первая строка таблицы.
• Третья строка обновляет указанную строку. В этом случае добавьте 5 к значению в столбце v строки.
• Четвертая строка, COMMIT; фиксирует транзакцию.

Если бы мы выполнили эту транзакцию в нашем примере таблицы, результат был бы таким:

Однако здесь важно то, что строка SELECT … FOR UPDATE (строка 2) заблокировала строку, которую обновила транзакция. Если другая транзакция (назовем ее Tx 2) попадает в базу данных, пытаясь работать с той же строкой во время обработки этой транзакции (Tx 1), Tx 2 будет добавлена ​​в очередь для обработки после того, как Tx 1 зафиксируется, а не начнет выполняться. , сбой и необходимость повторной попытки, потому что Tx 1 изменил одно из значений, к которым Tx 2 обращался во время обработки.

Параметры SELECT FOR UPDATE

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

Распространенным, например, является SELECT … FOR SHARE , который обеспечивает более слабую форму блокировки строк в некоторых системах баз данных. В PostgreSQL SELECT … FOR UPDATE полностью блокирует соответствующие строки, тогда как SELECT … FOR SHARE блокирует соответствующие строки только для обновлений и удалений.

Напротив, поскольку CockroachDB всегда гарантирует сериализуемую изоляцию и не существует «более слабых» уровней блокировки, SELECT … FOR SHARE функционирует идентично SELECT … FOR UPDATE . Синтаксис FOR SHARE поддерживается только для совместимости с Postgres.

Другим распространенным параметром является NOWAIT , который немедленно возвращает ошибку, если транзакция не может немедленно заблокировать строку. В синтаксисе SQL NOWAIT появляется сразу после 9.0009 ДЛЯ ОБНОВЛЕНИЯ , например:

 SELECT * FROM kv WHERE k = 1 FOR UPDATE NOWAIT;
 

SKIP LOCKED также является параметром, поддерживаемым некоторыми базами данных, который позволяет ожидающим транзакциям временно пропускать заблокированные строки, чтобы «удержание» этих строк не замедляло обработку элементов транзакции, влияющих на незаблокированные строки. В настоящее время CockroachDB не поддерживает это, отчасти потому, что использует многоверсионный контроль параллелизма. В этой ветке форума есть отличная информация для достижения тех же целей без использования ПРОПУСК ЗАБЛОКИРОВАН .

7 причин, по которым использование SELECT * FROM TABLE в SQL-запросе является плохой идеей

Я читал много статей в Интернете, в которых люди утверждают, что использование SELECT * в SQL-запросе является плохой практикой, и вам всегда следует избегать этого. Вместо этого всегда следует использовать явный список столбцов. Это хорошее предложение и одна из лучших практик SQL, которой я обучаю младших разработчиков, но многие из них не объясняют причину этого.

Если вы не объясните некоторые причины, по которым не следует использовать SELECT * в запросах, трудно убедить многих разработчиков SQL, многие из которых начали изучать SQL, выполняя SELECT * из EMP в базе данных Oracle.

В этой статье я попытаюсь заполнить этот пробел, приведя несколько практических причин, почему использование SELECT * в Query — плохая идея.

Вот несколько причин, по которым вам не следует использовать SELECT * из таблицы в вашем SQL-запросе.

1. Ненужный ввод-вывод (ввод-вывод)

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

Это также может немного замедлить ваш запрос. Если вы не знаете, как выполняется ваш запрос, как обработчик запросов обрабатывает ваш запрос, в каком порядке выполняются предложения и т. д., я предлагаю вам прочитать хорошую книгу, например «Производительность SQL, объясненная Маркусом Винандом» или «Полный курс SQL BootCamp» на Udemy. Узнать больше.

2. Увеличение сетевого трафика

SELECT * явно возвращает клиенту больше данных, чем требуется, что, в свою очередь, будет использовать большую пропускную способность сети. Это увеличение пропускной способности сети также означает, что данным потребуется больше времени для достижения клиентского приложения, которое может быть вашим собственным компьютером, если вы выполняете свой запрос в редакторе запросов, таком как SQL Server Management Studio, Toad или SQL Developer для Oracle. или ваш сервер приложений Java.

3. Больше памяти приложения

Из-за увеличения объема данных вашему приложению может потребоваться больше памяти только для хранения ненужных данных, которые оно не будет использовать, а будет поступать с Microsoft SQL Server.

4. Зависимость от порядка столбцов в ResultSet

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

5. Прерывает представления при добавлении новых столбцов в таблицу

Когда вы используете SELECT * в представлениях, вы создаете незаметные ошибки, если новый столбец был добавлен, а старый удален из таблицы. Почему? Потому что ваше представление не сломается, а начнет возвращать неверный результат.

Во избежание этого всегда следует использовать WITHSCHEMABINDING с представлениями в базе данных SQL Server. Это также предотвратит использование SELECT * в представлениях.

6. Конфликты в запросе JOIN 

Когда вы используете SELECT * в запросе JOIN, вы можете создать сложности, когда несколько таблиц имеют столбцы с одинаковыми именами, например. статус, активность, имя и т. д.

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

7. Копирование данных из одной таблицы в другую

При использовании оператора SELECT * в операторе INSERT .. SELECT, который является распространенным способом копирования данных из одной таблицы в другую, вы потенциально можете скопировать неверные данные. в неверный столбец, если порядок столбцов в обеих таблицах не одинаков.

Некоторые программисты считают, что использование SELECT * по сравнению с SELECT 1 в предложении EXISTS быстрее, потому что синтаксическому анализатору запросов приходится выполнять дополнительную работу для проверки статического значения.

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