September 1, 2018 at 6:20 pm
Hi,
My table as followCREATE TABLE [dbo].[crpt_PenyataPendapatanPerbelanjaan_2_Horizontal_1](
[idx] [int] IDENTITY(-2147483648,1) NOT NULL,
[batch_Id] [uniqueidentifier] NULL,
[group_PTJ_Idx] [nvarchar](50) NULL,
[group_PTJ_desc] [nvarchar](50) NULL,
[group_1Digit] [char](5) NULL,
[group_1Digit_desc] [nvarchar](50) NULL,
[group_1Digit_desc_Ext_1] [nvarchar](200) NULL,
[desc_text] [nvarchar](300) NULL,
[Detail_Line_Number] [tinyint] NULL,
[detail_note_number_for_program] [nvarchar](50) NULL CONSTRAINT [DF_crpt_PenyataPendapatanPerbelanjaan_2_Horizontal_1_detail_note_number_for_program] DEFAULT ((0)),
[detail_note_number] [tinyint] NULL,
[amt_dr] [decimal](18, 2) NULL,
[amt_cr] [decimal](18, 2) NULL,
[Console_Ref] [nvarchar](50) NULL,
[amt_year1] [decimal](18, 2) NULL,
[amt_year2] [decimal](18, 2) NULL,
[as_amt_year1] [decimal](18, 2) NULL,
[as_amt_year2] [decimal](18, 2) NULL,
[kump_amt_year1] [decimal](18, 2) NULL,
[kump_amt_year2] [decimal](18, 2) NULL,
[amt_year1_dr_cr] [decimal](18, 2) NULL CONSTRAINT [DF_crpt_PenyataPendapatanPerbelanjaan_2_Horizontal_1_amt_year1_dr_cr] DEFAULT ((0.00)),
[amt_year2_dr_cr] [decimal](18, 2) NULL CONSTRAINT [DF_crpt_PenyataPendapatanPerbelanjaan_2_Horizontal_1_amt_year2_dr_cr] DEFAULT ((0.00)),
[kump_amt_year1_dr_cr] [decimal](18, 2) NULL CONSTRAINT [DF_crpt_PenyataPendapatanPerbelanjaan_2_Horizontal_1_kump_amt_year1_dr_cr] DEFAULT ((0.00)),
[kump_amt_year2_dr_cr] [decimal](18, 2) NULL CONSTRAINT [DF_crpt_PenyataPendapatanPerbelanjaan_2_Horizontal_1_kump_amt_year2_dr_cr] DEFAULT ((0.00)),
CONSTRAINT [PK_crpt_PenyataPendapatanPerbelanjaan_2_Horizontal_1] PRIMARY KEY NONCLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
At Session A, I've simulation transaction as followuse SPKU_DB
Begin transaction
Begin Try
while (1=1)
Begin
insert into crpt_PenyataPendapatanPerbelanjaan_2_Horizontal_1(batch_Id) values(newid());
End
COMMIT transaction
End Try
Begin Catch
-- Whoops, there was an error
--IF @@TRANCOUNT > 0
ROLLBACK transaction
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
End Catch
At Session B, I've simulation transaction as followuse SPKU_DB
delete from crpt_PenyataPendapatanPerbelanjaan_2_Horizontal_1
where batch_Id = 'B05CD971-D267-46F4-9EBA-CEA686F04D65'
and amt_year1=0.00 and amt_year2=0.00;
My question is
(1) Why use SPKU_DB
delete from crpt_PenyataPendapatanPerbelanjaan_2_Horizontal_1
where batch_Id = 'B05CD971-D267-46F4-9EBA-CEA686F04D65'
and amt_year1=0.00 and amt_year2=0.00;
only can perform after transaction at Session A is commit?
(2) How use SPKU_DB
delete from crpt_PenyataPendapatanPerbelanjaan_2_Horizontal_1
where batch_Id = 'B05CD971-D267-46F4-9EBA-CEA686F04D65'
and amt_year1=0.00 and amt_year2=0.00;
can perform without waiting transaction at Session A is commit?
batch_Id = 'B05CD971-D267-46F4-9EBA-CEA686F04D65' is unique batch and not reflect any data in crpt_PenyataPendapatanPerbelanjaan_2_Horizontal_1
Please help
September 2, 2018 at 1:42 am
Session A is making multiple changes to a table. Very quickly SQL Server will have moved from row locks, onto page locks and ultimately will simply take a full table lock to avoid the overhead of managing data locking (this is known as lock escalation). By the time you run Session B, therefore, the table will be locked and it will have to wait before it can make any modifications.
One way to reduce this impact on concurrency is to use one of the snapshot isolation modes which use copies of data in tempdb rather than locks to handle data modification, but it comes with some overhead (given all the copies of data) and can change the behaviour of a system if it currently relies upon blocking queries to order changes.
September 2, 2018 at 2:37 am
andycadley - Sunday, September 2, 2018 1:42 AMSession A is making multiple changes to a table. Very quickly SQL Server will have moved from row locks, onto page locks and ultimately will simply take a full table lock to avoid the overhead of managing data locking (this is known as lock escalation). By the time you run Session B, therefore, the table will be locked and it will have to wait before it can make any modifications.One way to reduce this impact on concurrency is to use one of the snapshot isolation modes which use copies of data in tempdb rather than locks to handle data modification, but it comes with some overhead (given all the copies of data) and can change the behaviour of a system if it currently relies upon blocking queries to order changes.
My new codeuse SPKU_DB
set transaction isolation level snapshot
Begin transaction
Begin Try
while (1=1)
Begin
insert into crpt_PenyataPendapatanPerbelanjaan_2_Horizontal_1(batch_Id) values(newid());
End
COMMIT transaction
End Try
Begin Catch
-- Whoops, there was an error
--IF @@TRANCOUNT > 0
ROLLBACK transaction
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
End Catch
It cool
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply