Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Плз помогите с дедлоком/как селектить кучу однотипных таблиц  [new]
Romik76
Member

Откуда:
Сообщений: 231
Всем привет! буду крайне признателен за помощь.
Задача : есть база из однотипных (после,но не во время, создания клиентским приложением их структура идентична) таблиц, которые могут создаваться и удаляться клиентским приложением.
Нужно : создать представление Union с селектом по всем таблицам.
Варианты решения :
  • Существующий. Триггер в базе на DROP_TABLE, ALTER_TABLE. триггер запускает хранимую процедуру, которая создает (Alter View) желаемое представление по списку таблиц базы. Этот вариант напрягает ошибками в логе клиентского приложения типа
    Transaction (Process ID 261) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
    , когда клиент меняет одной структуру таблицы. Кроме того, не уверен, что это - правильно.
  • Пробуемый сейчас. Хочется создать что-то типа User-Defined функции , возвращающей табличное значение. Пытаюсь так :
    ALTER FUNCTION [dbo].[AllTables_Draft1]
    (
    	
    )
    RETURNS 
    @AllTables_Draft TABLE 
    (
    [TABLENAME] NVARCHAR(128)NOT NULL,
    [Baza] [varchar](20) NOT NULL,
    <тут разные поля таблиц базы>
    )
    AS
    BEGIN
    
    
    	DECLARE @DBTableVar NVARCHAR(128)
    	DECLARE @STATEMENT1 NVARCHAR(MAX),@START NVARCHAR(100)
    	,@UNION NVARCHAR(100),@EXEC_STATEMENT NVARCHAR(100)
    	,@ADDON NVARCHAR(100)
    	
    	
    	SET @STATEMENT1 =''
    	
    	SEt @UNION =''
    	
    	SET @addon =''
    
    	DECLARE AllTables CURSOR FOR
    	SELECT [name] FROM  Spec_rus.sys.[sysobjects] where (([xtype]='u')  and ([category]<>2)  ) ORDER BY [name];
    	OPEN AllTables	
    	
    	
    	
    	FETCH NEXT FROM AllTables INTO @DBTableVar
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    		 
    
    
    	SET @STATEMENT1 = @STATEMENT1+@UNION+ 'SELECT ' +CHAR(39)+@DBTableVar+CHAR(39)+' AS TABLENAME, '+CHAR(39)+'ццц '+CHAR(39)+' AS Baza,* '+@ADDON+' FROM dbo.['+ +@DBTableVar+']'
    	
    	SET @UNION =' UNION ALL '
    	
    	
    	SET @UNION =' UNION ALL '
    
    	
    		FETCH NEXT FROM AllTables INTO @DBTableVar;
    
    	END;
    
    	CLOSE AllTables;
    	
    	DEALLOCATE AllTables;
    	
    	
    	
    	EXECUTE sp_executesql  @STATEMENT1 
    
    	
    	RETURN 
    END
    
    
    результат SELECT * FROM dbo.AllTables_Draft1() -
    Msg 557, Level 16, State 2, Line 1
    Only functions and some extended stored procedures can be executed from within a function.

  • Создавать вьюшку из клиентского приложения, переписав его. Возможный вариант. Но все равно интересно другое решение.

    Что делать? Как-то поиграться с транзакциями в первом варианте или есть возможность применить функцию??

    Заранее большое спасибо!
  • 20 окт 11, 17:18    [11474408]     Ответить | Цитировать Сообщить модератору
     Re: Плз помогите с дедлоком/как селектить кучу однотипных таблиц  [new]
    Romik76
    Member

    Откуда:
    Сообщений: 231
    вот текст хранимой процедуры, которая вызывает дедлок
    ALTER PROCEDURE [dbo].[CreateAllTablesView]	
    AS
    BEGIN
    	
    	SET NOCOUNT ON;
    
    	DECLARE @DBTableVar NVARCHAR(128)
    	DECLARE @STATEMENT1 NVARCHAR(MAX),@START NVARCHAR(100),@UNION NVARCHAR(100),@EXEC_STATEMENT NVARCHAR(100)
    	
    	
    	
    
    	DECLARE AllTables CURSOR FOR
    	
    	SELECT [name] FROM  Spec_Rus.sys.[sysobjects] where (([xtype]='u')  and ([category]<>2)  AND (RIGHT([name], 4) = '#PIP')) ORDER BY [name];
    	OPEN AllTables
    
    	
    	
    	
    	SET @START = 'ALTER VIEW [dbo].[AllTables_Draft] AS '
    
    	SET @STATEMENT1 = @START
    	
    	FETCH NEXT FROM AllTables INTO @DBTableVar
    	WHILE (@@FETCH_STATUS = 0)
    	BEGIN
    	--PRINT N'Обрабатывается ' + @DBTableVar
    
    
    	if 	(@START = @STATEMENT1) SET @UNION ='' else SET @UNION =' UNION ALL '	
    
    
    	SET @STATEMENT1 = @STATEMENT1+@UNION
    + 'SELECT' +CHAR(39)+@DBTableVar+CHAR(39)+' AS TABLENAME, '+CHAR(39)+'SPEC_RUS'+CHAR(39)+' AS Baza,* FROM dbo.['+ +@DBTableVar+']'
    
    	
    		FETCH NEXT FROM AllTables INTO @DBTableVar;
    
    	END;
    
    	CLOSE AllTables;
    	
    	DEALLOCATE AllTables;
    	
    	set @EXEC_STATEMENT= '[dbo].[sp_executesql]'	
    
    	EXEC  @EXEC_STATEMENT  @STATEMENT1 ;
    	
       
    END

    Где бы тут транзакцию приткнуть? так что-ли
    begin try
    EXEC  @EXEC_STATEMENT  @STATEMENT1 ;
    Commit;
    end try
    begin catch
    rollback;
    end catch
    
    20 окт 11, 17:43    [11474672]     Ответить | Цитировать Сообщить модератору
     Re: Плз помогите с дедлоком/как селектить кучу однотипных таблиц  [new]
    komrad
    Member

    Откуда:
    Сообщений: 5738
    Romik76,

    мысль:
    убрать курсор
    список нужных таблиц селектить во времянку
    по времянке сделать цикл, в котором
    выбирать по одной таблице (сортировка по имени)
    собирать в переменную @cmd текст создания вьюхи
    удалять запись из времянки
    по окончанию цикла сделать exec (@cmd)
    20 окт 11, 18:03    [11474848]     Ответить | Цитировать Сообщить модератору
     Re: Плз помогите с дедлоком/как селектить кучу однотипных таблиц  [new]
    baracs
    Member

    Откуда: Москва
    Сообщений: 7217
    komrad
    Romik76,

    мысль:
    убрать курсор
    список нужных таблиц селектить во времянку
    по времянке сделать цикл, в котором
    выбирать по одной таблице (сортировка по имени)
    собирать в переменную @cmd текст создания вьюхи
    удалять запись из времянки
    по окончанию цикла сделать exec (@cmd)
    +1
    20 окт 11, 18:15    [11474894]     Ответить | Цитировать Сообщить модератору
     Re: Плз помогите с дедлоком/как селектить кучу однотипных таблиц  [new]
    Romik76
    Member

    Откуда:
    Сообщений: 231
    komrad,
    Спасибо за ответ.
    Думаете, что в многопользовательской среде - а именно там и возникает дедлок, курсор вызывает проблемы?
    То есть один клиент сделал Alter Table и триггер запустил хранимку. И одновременно второй чел сделал Alter Table и триггер запустил хранимку. И тут курсор в хранимке первого чела натыкается на табличку второго, которую он Alter, да?
    20 окт 11, 18:17    [11474910]     Ответить | Цитировать Сообщить модератору
     Re: Плз помогите с дедлоком/как селектить кучу однотипных таблиц  [new]
    komrad
    Member

    Откуда:
    Сообщений: 5738
    т.е. как-то так


    -- создаем таблицы
    create table table_1 (i int identity, val varchar(20))
    go
    create table table_2 (i int identity, val varchar(20))
    go
    create table table_3 (i int identity, val varchar(20))
    go
    create table table_4 (i int identity, val varchar(20))
    go
    create table table_5 (i int identity, val varchar(20))
    go
    
    -- заполняем данными
    insert into table_1 select replicate('1',20)
    go 20
    insert into table_2 select replicate('2',20)
    go 20
    insert into table_3 select replicate('3',20)
    go 20
    insert into table_4 select replicate('4',20)
    go 20
    insert into table_5 select replicate('5',20)
    go 20
    
    -- основной скрипт
    declare @cmd varchar(2048)
    declare @tbl varchar(20)
    select name into #tbl
    from sys.objects  where type='U' and name like 'table%'
    select @cmd='',@tbl=''
    while exists (select top 1 1 from #tbl)
       begin
    	select top 1 @tbl=name from #tbl order by name
    	
    	if @cmd<>'' select @cmd=@cmd+' union all '
    	select @cmd=@cmd + 'select '''+@tbl+''' [table],i,val from '+@tbl
    	delete #tbl where name=@tbl
    	select @tbl=''
      end
     select @cmd='create view all_tables as '+@cmd
    exec(@cmd)
    
    -- проверка и чистка объектов
    select * from all_tables 
    drop view all_tables 
    drop table #tbl
    
    20 окт 11, 18:18    [11474914]     Ответить | Цитировать Сообщить модератору
     Re: Плз помогите с дедлоком/как селектить кучу однотипных таблиц  [new]
    Romik76
    Member

    Откуда:
    Сообщений: 231
    komrad,

    Большое спасибо!

    Попробую сначала STATIC LOCAL для курсора, потом Ваш вариант
    20 окт 11, 18:44    [11475044]     Ответить | Цитировать Сообщить модератору
     Re: Плз помогите с дедлоком/как селектить кучу однотипных таблиц  [new]
    komrad
    Member

    Откуда:
    Сообщений: 5738
    Romik76
    komrad,

    Большое спасибо!

    Попробую сначала STATIC LOCAL для курсора, потом Ваш вариант


    курсор нафиг
    не нужен он здесь
    20 окт 11, 18:58    [11475114]     Ответить | Цитировать Сообщить модератору
     Re: Плз помогите с дедлоком/как селектить кучу однотипных таблиц  [new]
    iljy
    Member

    Откуда:
    Сообщений: 8711
    Romik76,

    а вы граф дедлока смотрели? Сильно подозреваю, что курсор тут ну вообще не при чем, так ка триггер вызывается после события, следовательно данные об изменяемой таблице уже заблокированы, а триггер пытается читать оттуда инфу. И если 2 триггера срабатывают, то заблокированы уже 2 строки, и вот дедлок как он есть.
    Вообще говоря, решение довольно странное. Пользователь имеет доступ на создание-удаление таблиц, не слишком ли? Я не буду обсуждать саму архитектуру, ваше дело, но я бы дал доступ только к процедуре, внутри которой делал все модификации.
    20 окт 11, 19:00    [11475127]     Ответить | Цитировать Сообщить модератору
     Re: Плз помогите с дедлоком/как селектить кучу однотипных таблиц  [new]
    Romik76
    Member

    Откуда:
    Сообщений: 231
    iljy,
    Хорошая идея. Подумаю, спасибо
    21 окт 11, 15:53    [11479944]     Ответить | Цитировать Сообщить модератору
     Re: Плз помогите с дедлоком/как селектить кучу однотипных таблиц  [new]
    iljy
    Member

    Откуда:
    Сообщений: 8711
    Romik76,

    подумайте. И если надумаете, то процедуру начните примерно так:
    BEGIN TRAN
    EXEC('ALTER VIEW .... AS SELECT 1')
    , тогда получите гарантированный монопольный захват вьюхи и отсутствие конфликтов. Иначе можете влегкую получить ситуацию, особенно если задействуете времянку, когда два пользователя меняют структуру, после чего каждый пересоздает вьюху с учетом только своих изменений. Тогда кто последний, тот и папа, но вьюха будет некорректная и ляжет вся система.
    21 окт 11, 16:06    [11480086]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить