Locking: PeopleSoft на SQL Server

[Kon]

Имеем PeopleSoft на SQL Сервере. Когда одновременно гоняются reports и транзакции то происходит постоянный blocking и иногда даже deadlocks. Понятно что вызванно это shared locks на таблицах где гоняется репорт, но как этого избежать? Кто нибудь сталкивался с этой проблемой и как ее решить?

[Dmitry67]

Наверное это в IT технологии

По делу
Так как переделать код PeopleSoft генерящий запросы вы не можете то похоже остается кардинальное решение

Поставить второй сервер
Превратить его в disaster recovery server
Отливать на него копию базы
Строить отчеты на этом сервере. Если есть OLAP, То его туда же
В принципе можно делать все и на одном сервере

Если такое подойдет то могу написать про детали

Есть еще трюкаческое решение... Расскажите про систему отчетов - как они описываются, как строются... Можно ли в них "влезть" ? Можно ли отчеты запускать от имени какого либо пользователя ?

В отчетах делается ли select ... from ИмяТаблицы
или
select ... from dbo.ИмяТаблицы (вместо dbo может быть какой нибудь другой пользователь)

[Kon]

Идея со вторым сервером не годится так как я не могу гарантировать real-time updates. Пользователи требуют самую свежую инфомацию, максимум 5 минут старше чем база с транзакциями.

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

Я также пытался уговорить пользователей позволить dirty reads, но это тоже запретили, официальный ответ от PeopleSoft это только Read Committed.

отчеты выглядят как select * from dbo.table

[zVlad]

Kon, did you look at replication? I guess, replication can keep your databases in sync and help to relief from locking problems.
In our shop, we have this approach implemented to satisfy both OLTP and reporting.

[Kon]

Replication wouldn't work, since the reports would hold the same shared locks as they do in the OLTP right now. Replication agent will sit and wait for the locks to be released. This can be very quick, but I can not guarantee that the latency will be under 5 minutes.

[zVlad]

Kon, тогда получается что решения нет. Вот если бы у MSSQL был бы уровень изоляции CS (Cursor Stability) или Оракловская версионность, то тогда было бы легче. Точнее проблемы стали бы другими. Вообще интересно было бы посмотреть, как ваша нагрузка (OLTP and reporting) повели бы себя на DB2 и Оракл.

[Kon]

На oracle етой проблемы б не было б вообще, но oracle is not an option. Я не понимаю как другие PeopleSoft shops работают.

[Dmitry67]

Да, многие возможности отпали
Вопросы

1. А сколько таблиц проблемные ?
2. Можете ли вы менять таблицы ? Типа добавить новое поле ?

P.S
Интересно а что говорит сам support PeopleSoft ?

[zVlad]

Kon, вспоминая недавние дискусии на подобную тему, я бы сказал что в Оракл-е проблемы будут выглядеть иначе. В вашем случае самая вероятная проблема была бы проблема связанная с тем что результирущее множество запроса для репорта может быть модифицированно онлайн транзакциями. И единственным выходом из этой проблемы могло бы остаться использование уровня изоляции SERIALIZABLE. И все бы вернулось на те же круги. Если не хуже.
А к DB2 у вас как относятся?

[Леший]

Я не понимаю как другие PeopleSoft shops работают.

В основом на оракле и работают.

[Леший]

Да, многие возможности отпали
Вопросы

1. А сколько таблиц проблемные ?
2. Можете ли вы менять таблицы ? Типа добавить новое поле ?

P.S
Интересно а что говорит сам support PeopleSoft ?

Дима если PeopleSoft implementation близка к ваниле, то не один прожект манаджер не пойдет на то чтобы модифицировать таблицы , unless там какие-то баги например с key structure. И в 90% случаев ( по крайне мере у нас ) подобные баги просто постят в PeopleSoft и ждут пока там починят.

Что говорит PeopleSoft действительно интересно. Вы у них case по этому поводу открыли ?

[Kon]

Только что закончил телефонную конференцию с MS и PeopleSoft.
Каждый катит бочки друг на друга.
Окончательный ответ это что мне надо ещё лучше настроить сервер что бы
сократить время транзакции и соответственно сократить жизнь locka. Таблицы я менять
не могу, так как я не смогу upgrade в будущем. Eдинственное что я могу менять это индеkсы.
PeopleSoft предложил поставить побольше индеkсoв, МС сразу предложил не забывать что у меня смешанная, OLTP/reporting, база и
что много индехов это плохо. Таблиц у меня кстати 40000, всего 120000 обектов, правда реально используется только ~200.

МС также сказал что 4 недели назад Хилтон Хотелс поставил себе PeopleSoft на SQL Сервер и что у них нет никаких проблем несмотря на то что у них тоже mixed environment.

[zVlad]

Kon, I would still recommend to get closer look at replication. You say: "Replication wouldn't work, since the reports would hold the same shared locks as they do in the OLTP right now. Replication agent will sit..."
Replication agent, but not transactions! Transactions will run without contentions with reporting ".... and wait for the locks to be released. This can be very quick, but I can not guarantee that the latency will be under 5 minutes."
Any solutions, but dirty read, will give you unpredictable latency. Even though you say Oracle doesn't have such problems (locking problems), actually in case of Oracle you have the same latency because your report query will run over "freezed" (at a time when report starts) data. Look, Oracle with it's versionig is quite similar to what replication gives us here. Remember, transactions ("writers") do not block "readers", and "readers" do not block "writers", right?

[Dmitry67]

Репликация в MS SQL довольно отстойная и наверняка потребует модификации таблиц...

По поводу индексов я бы подумал
Вот запустите такой процесс

Создайте таблицу
Сделайте тест во время которого запустите процедуру LockWatcher
Потом остановите ее
Запустите LockRanges
Она покажет кто и кого и сколько ждал

Копайте подробнее по найденным таблицам

Code: Select all

CREATE TABLE ProfilerLockLog (
  spid     smallint NULL ,
  dbid     smallint NOT NULL ,
  ObjId    int NOT NULL ,
  IndId    smallint NOT NULL ,
  Type     nvarchar (4) NULL ,
  Resource nvarchar (16) NULL ,
  Mode     nvarchar (8) NULL ,
  Status   nvarchar (5) NULL ,
  name     varchar (128) NULL ,
  temps    datetime NULL 
) 
GO

create procedure LockWatcher
as
  set nocount on
  create table #SpecStatistic (
   spid smallint NULL ,
   dbid smallint NOT NULL ,
   ObjId int NOT NULL ,
   IndId smallint] NOT NULL ,
   Type varchar (4) ,
   Resource varchar (16) ,
   Mode varchar (8)  NULL,
   Status varchar (5) NULL )
loop:
  truncate table #SpecStatistic
  insert into #SpecStatistic (spid,dbid,objid,indid,type,resource,mode,status)
   select    convert (smallint, req_spid) As spid,
      rsc_dbid As dbid,
      rsc_objid As ObjId,
      rsc_indid As IndId,
      substring (v.name, 1, 4) As Type,
      substring (rsc_text, 1, 16) as Resource,
      substring (u.name, 1, 8) As Mode,
      substring (x.name, 1, 5) As Status

   from    master.dbo.syslockinfo,
      master.dbo.spt_values v,
      master.dbo.spt_values x,
      master.dbo.spt_values u

   where   master.dbo.syslockinfo.rsc_type = v.number
         and v.type = 'LR'
         and master.dbo.syslockinfo.req_status = x.number
         and x.type = 'LS'
         and master.dbo.syslockinfo.req_mode + 1 = u.number
         and u.type = 'L'
         and x.name<>'GRANT'
   order by spid

  -- tempdb
  insert into ProfilerLockLog (spid,dbid,objid,indid,type,resource,mode,status,name,temps)
  select spid,dbid,objid,indid,S.type,resource,mode,S.status,O.name,getdate() 
    from #SpecStatistic S, tempdb.dbo.sysobjects O
    where ObjId>0 and dbid=2 and id=ObjId and O.name not like '%SpecStatistic%'

  -- this db
  insert into ProfilerLockLog (spid,dbid,objid,indid,type,resource,mode,status,name,temps)
  select spid,dbid,objid,indid,S.type,resource,mode,S.status,O.name,getdate() 
    from #SpecStatistic S, sysobjects O
    where ObjId>0 and dbid=db_id() and id=ObjId

  waitfor delay '00:00:00.010'
  goto loop
GO

create procedure LockRanges
as
  CREATE TABLE #id (
    n        int identity,
    spid     smallint NULL ,
    ObjId    int NOT NULL ,
    IndId    smallint NOT NULL ,
    Type     nvarchar (4) NULL ,
    Resource nvarchar (16) NULL ,
    Mode     nvarchar (8) NULL ,
    name     varchar (128) NULL ,
    temps    datetime NULL 
  ) 

  CREATE TABLE #id2 (
    n        int identity,
    spid     smallint NULL ,
    ObjId    int NOT NULL ,
    IndId    smallint NOT NULL ,
    Type     nvarchar (4) NULL ,
    Resource nvarchar (16) NULL ,
    Mode     nvarchar (8) NULL ,
    name     varchar (128) NULL ,
    tbeg     datetime NULL,
    tend     datetime NULL,
    ntoset   int NULL
  ) 

  insert into #id (spid,ObjId,IndId,Type,Resource,Mode,name,temps) 
    select spid,ObjId,IndId,Type,Resource,Mode,name,temps from ProfilerLockLog order by spid,temps

  delete from #id where 
    exists(select * from #id N where
      N.spid=#id.spid and N.ObjId=#id.ObjId and N.Type=#id.Type and N.Resource=#id.Resource and N.Mode=#id.Mode 
      and N.n=#id.n+1)
    and
    exists(select * from #id N where
      N.spid=#id.spid and N.ObjId=#id.ObjId and N.Type=#id.Type and N.Resource=#id.Resource and N.Mode=#id.Mode 
      and N.n=#id.n-1)

  insert into #id2 (spid,ObjId,IndId,Type,Resource,Mode,name,tbeg) 
    select spid,ObjId,IndId,Type,Resource,Mode,name,temps from #id order by spid,n
  update #id2 set tend=(select tbeg from #id2 N where 
      N.spid=#id2.spid and N.ObjId=#id2.ObjId and N.Type=#id2.Type and N.Resource=#id2.Resource and N.Mode=#id2.Mode 
      and N.n=#id2.n+1)
  delete from #id2 where tend is NULL

  select name,spid,indId,Type,Resource,Mode,tbeg,tend,datediff(ms,tbeg,tend) as Dur_ms from #id2

GO

[Kon]

Спасибо Dmitry67, попробую.

[Kon]

Поговорил с Хилтоном. У них были те же проблемы что и у меня. После долгих проб и ошибок они решили проблему.

1) Включить флаг 1211 что бы выключить lock escalation.. Это создает добавочные проблемы с памятью и можно упереться в максимум дозволенных locks. Но это меньшее из зол. Ещё можно добавить /ЗGB
параметер в boot.ini что бы дать серверу больше памяти для оптимизатора и locks.

2) Создать добавочные файлы для tempdb. Количество файлов =2 х количество CPU. Все файлы должны быть
одного размера. Это поможет избежать waits в tempdb связанных сo cмешанными extents.

3) Выставить Network Packet size = 8192

4) Выставить Max Degree of Parallelism = 1

5) Также они предложили форматировать мой диск с 64К allocation unit size, что бы оно равнялось размеру extenta в SQL Servere.
Но мне кажется что это излишне.

[ie]

1) Включить флаг 1211 что бы выключить lock escalation.. Это создает добавочные проблемы с памятью и можно упереться в максимум дозволенных locks. Но это меньшее из зол. Ещё можно добавить /ЗGB
параметер в boot.ini что бы дать серверу больше памяти для оптимизатора и locks.

Kon, что такое boot.ini ?

[Леший]

1) Включить флаг 1211 что бы выключить lock escalation.. Это создает добавочные проблемы с памятью и можно упереться в максимум дозволенных locks. Но это меньшее из зол. Ещё можно добавить /ЗGB
параметер в boot.ini что бы дать серверу больше памяти для оптимизатора и locks.

Kon, что такое boot.ini ?

Это файл ie, это файл ..

[ie]

Это файл ie, это файл ..

Спасибо за конструктивный ответ, smart @ss

[Леший]

Это файл ie, это файл ..

Спасибо за конструктивный ответ, smart @ss

Дык сам подставился .. зри в корень ( в плане с:\) если NT/2000