Meu banco de dados tem uma tabela: tableX.
Task1 faz INSERTs intensivos (1000 registros por minuto) como registros para processar e muito raramente UPDATEs (1-2 registros por minuto) como registros para recalcular.
Ao mesmo tempo, outro Task2 SELECTs não processa registros (inserido por Task1) e, em seguida, insere novos registros (conforme processados) na mesma tabelaX em uma transação (é um cálculo muito longo, até 10 segundos).
Task3 recalcula registros ATUALIZADOS (por Task1): apenas ATUALIZA tableX.
Alguém poderia recomendar um design para evitar longos bloqueios e impasses para este cenário. É muito importante processar todos os registros INSERIDOS na ordem em que foram inseridos!
1) Devo separar (criar uma nova tabelaX2) registros processados e não processados?
2) Devo separar a transação em Task1 e fazer SELECTs e INSERTs em duas transações separadas?
3) Devo usar rowlock, readpast...hints e onde (pode me ajudar)?
Simples este.
Task1 não deve inserir na tabela principal, mas em uma tabela de preparação, digamos tableXstaging. Essa tabela deve incluir uma coluna de identidade ou uma coluna row_processed (TINYINT ou BIT).
Task1 continua inserindo o tempo todo em tableXstaging. Eu permitiria atualizações para atualizar a tabela principal diretamente, pois são raras.
Outra tarefa que é executada uma vez a cada minuto: definirá MaxID = max(coluna de identidade) de tableXstaging ou, se você tiver usado row_processed, definirá row_processed = 1. Isso fornecerá o lote com o qual trabalhar. Esta tarefa irá inserir na tableX todos os registros onde identity<=MaxID, ou se você estiver usando row_processed, WHERE row_processed = 1. Uma única inserção em sua tableX primária é muito rápida. Em seguida, exclua de tableXstaging onde row_processed = 1 ou WHERE identity<=MaxID. Eu iria com identidade, pois você não precisa atualizar row_processed = 1 onde row_processed = 0 e, portanto, será mais rápido.
tableX agora é inserido apenas uma vez por minuto.
Na verdade, relendo sua pergunta, eu iria com row_processed, definir row_processed = 1 para seu primeiro lote.
A Tarefa 2 funciona na tabela de preparação WHERE row_processed = 1. Em seguida, define row_processed = 2.
A tarefa 3 funciona na tabela de preparação WHERE row_processed = 2. Em seguida, define row_processed = 3.
Finalmente, SET XACT_ABORT ON; BEGIN TRAN, você insere os registros na tableX em um hit (row_processed=3). Isso é muito eficiente, pois a tabela de preparação é pequena e a tableX será muito grande e lenta (em termos relativos à tabela de preparação). Em seguida, exclua do estágio onde row_processed = 3. COMMIT;