Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / вставка уникального значения. / 25 сообщений из 48, страница 1 из 2
  1  все
10.01.2020, 15:42
    #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:47
    #39912491
Shakill
Shakill
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вставка уникального значения.
andron81,

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

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

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


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


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


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


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


with (tablockx, holdlock)

[/src]
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2020, 16:17
    #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:18
    #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:33
    #39912522
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вставка уникального значения.
andron81,

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

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


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

ну в смысле поиском дыр я не озабочен.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2020, 19:21
    #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:26
    #39912603
Гавриленко Сергей Алексеевич
Гавриленко Сергей Алексеевич
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вставка уникального значения.
entrypoint,

Все это делается через sp_getapplock или tablockx.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2020, 20:11
    #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
| Ответить | Цитировать | Написать  
12.01.2020, 15: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:17
    #39912991
andron81
andron81
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вставка уникального значения.
andron81,

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

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

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

Монопольной блокировкой вы выстроите в очередь всех - и читателей, и писателей, и добавлятелей.
Если делать как было паказано в 22056821 - в очередь встанут только добавлятели, ну и некоторые писатели, если будут модифицировать/удалять строку с максимальным NUM.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2020, 19:27
    #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:57
    #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, 20:15
    #39913022
andron81
andron81
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вставка уникального значения.
invm, неа. мне показалось, что выше Вы писали иначе о том что будет происходить, если так расставить изоляцию
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.01.2020, 20:30
    #39913027
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
вставка уникального значения.
andron81,

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


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