ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / вставка уникального значения.
25 сообщений из 48, страница 1 из 2
  1  все
вставка уникального значения.
    #39912487
andron81
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andron81
Участник
Есть таблица с одним единственным числовым полем (NUM), это поле первичный ключ.
для неё делаю
Код: sql
1.
2.
truncate table SPK_TEST13 /*скрипт запускается много раз , поэтому очистим*/
insert into SPK_TEST13 (NUM) values (1)



Задача такая : хочу вставить туда строчку со значением поля равного max(NUM) +1 .
Как всегда начнете пинать, да и ладно , но где я не дочитал по транзакциям ???

сначала на одном сеансе запускаю :
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
BEGIN TRAN;
DECLARE @NEW_NUM INT;
SELECT @NEW_NUM = max(NUM)+1 FROM SPK_TEST13
select @NEW_NUM
INSERT INTO SPK_TEST13(NUM) VALUES (@NEW_NUM)

WAITFOR DELAY '00:00:30';

COMMIT TRAN;
go
SELECT * FROM SPK_TEST13



потом на 10 - ти других сеансах последовательно запускаю тоже самое , но без задержки WAITFOR DELAY '00:00:30';
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
BEGIN TRAN;
DECLARE @NEW_NUM INT;
SELECT @NEW_NUM = max(NUM)+1 FROM SPK_TEST13
select @NEW_NUM
INSERT INTO SPK_TEST13(NUM) VALUES (@NEW_NUM)

WAITFOR DELAY '00:00:30';

COMMIT TRAN;
go
SELECT * FROM SPK_TEST13



в результате на одном из сеансов инсерт отвалился, а почему ? :-(
Сообщение 2627, уровень 14, состояние 1, строка 11
Нарушение "PK_SPK_TEST13" ограничения PRIMARY KEY. Не удается вставить повторяющийся ключ в объект "dbo.SPK_TEST13". Повторяющееся значение ключа: (3).
...
Рейтинг: 0 / 0
10.01.2020, 15:42
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912491
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
andron81,

Код: sql
1.
SELECT @NEW_NUM = max(NUM)+1 FROM SPK_TEST13 with (tablockx, holdlock)
...
Рейтинг: 0 / 0
10.01.2020, 15:47
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912492
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
Для этого придумали:
  • identity
  • sequence
  • уровень транзакции serializeable или tablockx
...
Рейтинг: 0 / 0
10.01.2020, 15:48
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912493
andron81
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andron81
Участник
Гавриленко Сергей Алексеевич
Для этого придумали:
  • identity
  • sequence
  • уровень транзакции serializeable или tablockx

идентити это понятно. мне для понимания.
...
Рейтинг: 0 / 0
10.01.2020, 15:49
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912499
andron81
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andron81
Участник
andron81
Гавриленко Сергей Алексеевич
Для этого придумали:
  • identity
  • sequence
  • уровень транзакции serializeable или tablockx

идентити это понятно. мне для понимания.


что имеется ввиду ? sequence
...
Рейтинг: 0 / 0
10.01.2020, 16:03
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912501
andron81
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andron81
Участник
andron81
andron81
пропущено...


идентити это понятно. мне для понимания.


что имеется ввиду ? sequence


понятно, вы переоцениваете версию MS SQL
...
Рейтинг: 0 / 0
10.01.2020, 16:06
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912503
andron81
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andron81
Участник
спасибо , осталось понять вот это :
Shakill


with (tablockx, holdlock)

[/src]
...
Рейтинг: 0 / 0
10.01.2020, 16:08
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912510
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
Гавриленко Сергей Алексеевич
Для этого придумали:
  • identity
  • sequence
  • уровень транзакции serializeable или tablockx

Я так понял человек озабочен отсутствием дырок в последовательности, чего IDENTITY и SEQUENCE не гарантируют.

А если при Readcommitted делать merge запроса (SELECT max(NUM)+1 AS NEW_NUM FROM SPK_TEST13) на целевую таблицу? Этого не будет достаточно? Или будут дедлоки или дубли?
...
Рейтинг: 0 / 0
10.01.2020, 16:17
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912511
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
andron81
спасибо , осталось понять вот это :
Shakill

with (tablockx, holdlock)
Когда несколько транзакцией делают
Код: sql
1.
SELECT @NEW_NUM = max(NUM)+1 FROM SPK_TEST13


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

Хинт tablockx указывает, что нужно наложить на таблицу монопольную блокировку, и тогда первая транзакция будет держать таблицу, пока не закроет транзакцию, и остальные будут ждать.
...
Рейтинг: 0 / 0
10.01.2020, 16:18
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912522
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
andron81,

автор где я не дочитал по транзакциям

Не дочитали раздел об уровне изоляции транзакций.
...
Рейтинг: 0 / 0
10.01.2020, 16:33
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912523
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
alexeyvg
Хинт tablockx указывает, что нужно наложить на таблицу монопольную блокировку, и тогда первая транзакция будет держать таблицу, пока не закроет транзакцию, и остальные будут ждать.


точно, эксклюзивные блокировки и так держатся до конца транзакции. holdlock не нужен
...
Рейтинг: 0 / 0
10.01.2020, 16:33
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912526
andron81
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andron81
Участник
a_voronin


Я так понял человек озабочен отсутствием дырок в последовательности, чего IDENTITY и SEQUENCE не гарантируют.

А если при Readcommitted делать merge запроса (SELECT max(NUM)+1 AS NEW_NUM FROM SPK_TEST13) на целевую таблицу? Этого не


он прав идентити вполне пригоден для моей задачи. я забыл оговорить , что я его использовать не хочу. мне для понимания транзакции , поэтому я изобретаю этот велосипед. Поиском "несуществующих" id - шек в таблице с дальнейшем их инсертом задача не стоит, спасибо.
...
Рейтинг: 0 / 0
10.01.2020, 16:40
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912527
andron81
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andron81
Участник
andron81,

ну в смысле поиском дыр я не озабочен.
...
Рейтинг: 0 / 0
10.01.2020, 16:41
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912600
entrypoint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
entrypoint
Гость
andron81,

создаем объекты и наполняем данными
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
CREATE TABLE dbo.[data](id INT NOT NULL);
CREATE TABLE dbo.mutex(id INT NOT NULL);
INSERT INTO dbo.mutex(
       id
         )
VALUES( 
       0
       );



Для WAITFOR TIME 'Здесь указать время' указываем ближайшее время в будущем, для того что бы два одинаковых скрипта в разных окнах запросов (сессиях) запустились одновременно

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
WAITFOR TIME '19:09:00';

SET NOCOUNT, XACT_ABORT ON;
DECLARE @i INT = 0;

WHILE @i < 100
    BEGIN
        BEGIN TRANSACTION;
        IF EXISTS
                 (
                  SELECT TOP (1) 
                         1
                  FROM 
                       dbo.mutex WITH (UPDLOCK, ROWLOCK)
                  )
            BEGIN
                INSERT INTO dbo.[data]
                OUTPUT 
                       inserted.id
                VALUES( 
                       ISNULL(
                              (
                               SELECT 
                                      MAX(id) + 1
                               FROM 
                                    dbo.[data]
                       ), 1)
                       );
            END;
        COMMIT TRANSACTION;
        SET @i+=1;
    END;

SELECT 
       id
FROM 
     dbo.[data];



1. Создаем новый запрос, копируем туда выше приведённый скрипт с указанным временем WAITFOR TIME, запускаем
2. Создаем новый запрос, копируем туда выше приведённый скрипт с указанным временем WAITFOR TIME, запускаем
...
...
...
...
n. Ждем, когда настанет время старта, указанное Вами в WAITFOR TIME, и получаем результат

Это - потом
Код: sql
1.
2.
DROP TABLE dbo.[data]
DROP TABLE dbo.mutex
...
Рейтинг: 0 / 0
10.01.2020, 19:21
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912603
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
entrypoint,

Все это делается через sp_getapplock или tablockx.
...
Рейтинг: 0 / 0
10.01.2020, 19:26
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912622
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Shakill
точно, эксклюзивные блокировки и так держатся до конца транзакции. holdlock не нужен
Наоборот. tablockx - слишком жестоко.
Чтобы всех желающих выстроить в очередь достаточно
Код: sql
1.
select top (1) NUM + 1 from SPK_TEST13 with (updlock, serializable) order by NUM desc;
...
Рейтинг: 0 / 0
10.01.2020, 20:11
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912990
andron81
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andron81
Участник
alexeyvg

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


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

запускаю в первом сеансе (*)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
BEGIN TRAN;
DECLARE @NEW_NUM INT;
SELECT @NEW_NUM = max(NUM)+1 FROM SPK_TEST13 with (tablockx)
select @NEW_NUM
INSERT INTO SPK_TEST13(NUM) VALUES (@NEW_NUM)

WAITFOR DELAY '00:00:30';

COMMIT TRAN;
go
SELECT * FROM SPK_TEST13



не дожидаясь выполнения запускаю последовательно скрипт N раз в разных сеансах (в разных запросах менеджмент студио, но под одним пользователем):
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
BEGIN TRAN;
DECLARE @NEW_NUM INT;
SELECT @NEW_NUM = max(NUM)+1 FROM SPK_TEST13 with (tablockx)
select @NEW_NUM
INSERT INTO SPK_TEST13(NUM) VALUES (@NEW_NUM)
COMMIT TRAN;
go
SELECT * FROM SPK_TEST13



Но речь идет о монопольности. верно ли я понимаю, что раз я запускаю под одним пользователем , то возможны неприятности в виде :
"Нарушение "PK_SPK_TEST13" ограничения PRIMARY KEY. Не удается вставить повторяющийся ключ в объект "dbo.SPK_TEST13". Повторяющееся значение ключа: (3)."

Ведь в рамках одного пользователя можно читать таблицу из других транзакций. Или я неверно понимаю понятие монопольности ?
...
Рейтинг: 0 / 0
12.01.2020, 15:11
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912991
andron81
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andron81
Участник
andron81,

хотя вот оно определение монопольной блокировки:

монопольная (exclusive) - означает, что только одна сессия может установить эту блокировку в один момент времени.
ни о каких пользователях речи нет.
...
Рейтинг: 0 / 0
12.01.2020, 15:17
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39912996
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
andron81,

да, если один пользователь установит три сессии, то выполняться запрос будет только в одной, остальные сессии будут ожидать.
...
Рейтинг: 0 / 0
12.01.2020, 16:16
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39913009
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
andron81,

Монопольной блокировкой вы выстроите в очередь всех - и читателей, и писателей, и добавлятелей.
Если делать как было паказано в 22056821 - в очередь встанут только добавлятели, ну и некоторые писатели, если будут модифицировать/удалять строку с максимальным NUM.
...
Рейтинг: 0 / 0
12.01.2020, 18:05
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39913015
andron81
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andron81
Участник
invm

select top (1) NUM + 1 from SPK_TEST13 with (updlock, serializable) order by NUM desc;


invm

в очередь встанут только добавлятели, ну и некоторые писатели, если будут модифицировать/удалять строку с максимальным NUM.


а читателям карт бланш во всех транзах одновременно ?
дно мне ведь и читать нельзя тут в других транзах , если эта транза не активная на данный момент. иначе вот выполняется первая транзакция . прочитается в ней скажем max(NUM)=5 и во второй тоже max(NUM)=5 прочитается(читателям ведь можно), а потом когда придет время выполнения записи во второй транзакции , а NUM=5 уже записан. и вот вторая транзакция и отвалится.
или я неверно Вас понял ?
...
Рейтинг: 0 / 0
12.01.2020, 19:27
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39913019
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
andron81,

Код: sql
1.
2.
begin tran;
select top (1) @NUM = NUM from SPK_TEST13 with (updlock, serializable) order by NUM desc;


После выполнения этого запроса и до завершения транзакции, в других транзакциях невозможно будет прочитать, изменить, добавить или удалить те строки SPK_TEST13, где NUM >= @NUM
Это справедливо и для пустой таблицы. В этом случае можно считать, что в @NUM минус бесконечность.

Так понятнее?
...
Рейтинг: 0 / 0
12.01.2020, 19:57
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39913022
andron81
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andron81
Участник
invm, неа. мне показалось, что выше Вы писали иначе о том что будет происходить, если так расставить изоляцию
...
Рейтинг: 0 / 0
12.01.2020, 20:15
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39913027
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
andron81,

Не вижу разницы. "Добавлятель" - это ваш код и стартового поста.
...
Рейтинг: 0 / 0
12.01.2020, 20:30
| Ответить | Цитировать | Написать  
вставка уникального значения.
    #39913028
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Организация пользовательских счетчиков (генераторов) в Microsoft SQL Server
...
Рейтинг: 0 / 0
12.01.2020, 20:45
| Ответить | Цитировать | Написать  
25 сообщений из 48, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / вставка уникального значения.
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=39912503&tid=1686660]:
 0ms
get settings:
 12ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 37ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 55ms
get tp. blocked users:
 1ms
others: 19ms
total:  158ms
созд. / загр.: 158ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]