September 10, 2008 at 3:16 am
Hi Guys,
I am using INSERT trigger for one of my sales table. A problem I am getting is, this trigger is locking the table so that other transaction are not arrived to the sales table until trigger is completed.
Any way to get around this? How to unlock the table while triggers is fire so that all the transaction are keep coming to table?
Thanks.
Leo
September 10, 2008 at 7:44 am
Depends on what the trigger's doing.
Triggers run in the context of the transaction that was started by the statement that fired the trigger. That transaction is only committed after the trigger completes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 10, 2008 at 7:53 am
Hi,
I am using FOR INSERT.
Thanks.
Leo
September 10, 2008 at 8:04 am
You said before it was an insert trigger. Need to know a lot more about what the trigger's doing before I can say anything more than I have on the trigger's locking behaviour.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 10, 2008 at 8:31 am
CREATE TRIGGER dbo.Write_TEST_NEW ON dbo.TEST
FOR INSERT
AS
IF @@ROWCOUNT = 0RETURN
DECLARE @tbl_TEMP_TEST_TABLE TABLE( RowID int IDENTITY(1, 1),
Param1int,
Param2int,
Param3nvarchar(30),
GenTimedatetime)
DECLARE @ctr_TEMP_TEST_TABLE_CURRENTCOUNTER int , @ctr_TEMP_TEST_TABLE_MAXCOUNTER int
INSERT @tbl_TEMP_TEST_TABLE
SELECTParam1,
Param2,
Param3,
GenTime
FROMINSERTED
SELECT@ctr_TEMP_TEST_TABLE_CURRENTCOUNTER = MIN(RowID),
@ctr_TEMP_TEST_TABLE_MAXCOUNTER = MAX(RowID)
FROM@tbl_TEMP_TEST_TABLE
WHILE (Not @ctr_TEMP_TEST_TABLE_CURRENTCOUNTER Is Null) And (Not @ctr_TEMP_TEST_TABLE_CURRENTCOUNTER = 0) And (@ctr_TEMP_TEST_TABLE_CURRENTCOUNTER <= @ctr_TEMP_TEST_TABLE_MAXCOUNTER)
Begin
SELECT@DeviceID= Param1,
@SalesHolderID= Param2,
@SalesCardNo= Param3,
@EventTime =GenTime
FROM@tbl_TEMP_TEST_TABLE
WHERERowID = @ctr_TEMP_TEST_TABLE_CURRENTCOUNTER
SELECT @SalesCardNo = LTrim(RTrim(@SalesCardNo))
SELECT @FR_NUMBERS = dbo.SalesHolder.Note
FROMdbo.SalesHolder WITH (NOLOCK)
WHERE dbo.SalesHolder.RecordID = @SSalesCardNo
If @@ROWCOUNT <> 0
Begin
SELECT @CommandStr = @FR_NUMBERS
INSERT INTO dbo.SECOND_TABLE (TestString) Values @CommandStr)
End
SELECT@ctr_TEMP_TEST_TABLE_CURRENTCOUNTER = @ctr_TEMP_TEST_TABLE_CURRENTCOUNTER + 1
End
Here is my code what I wrote. all the variables are declared in my Database.
September 10, 2008 at 8:42 am
I can see why you have issues. This trigger is RBAR (a Modenism for Row By Agonizing Row). You really need to look at rewriting this trigger to accomplish what you need done using a set-based process instead of looping through the data row by row.
Not knowing exactly what you are trying to accomplish, and not knowing the structure of the tables involved, it is hard to tell you what I'd do in this case.
😎
September 10, 2008 at 8:53 am
I completely concur with Lynn, but to add to it, if the transaction being held up only needs to read from the locked table and not write to it, you can consider adding 'with (nolock)' to the reading transaction that is being blocked.
The with (nolock) table hint needs to be used very carefully since it does create the risk of certain types of inconsistencies, but in some cases those risks can be quite acceptable for the performance returns and blocking avoidance it can bring.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
September 10, 2008 at 9:01 am
First of all thaks to Lynn and Tim,
Tim,
what do you mean 'locked table and not write to it, you can consider adding 'with (nolock)' to the reading transaction that is being blocked.'
You mean, I shouldn't use 'with (nolock)' option.
Should I remove those 'with (nolock)'?
Lynn,
How can I accomplish with 'set-based process' instead of looping through the table?
Thanks again.
Leo
September 10, 2008 at 9:07 am
Need to know more to answwer your question. Need the DDL (create statements) for the tables involved, sample data for the tables (in the form of INSERT statements that can be cut, paste, and run to load the tables), expected results from the process so we can test what we write.
Also, a good explaination of what the trigger is trying to accomplish (call it requirements).
😎
September 10, 2008 at 9:15 am
Hello lynn,
Here is what I am trying to accomplish......
I have a table called 'SALES' Table which is getting new sales records transaction in every single seconds.
I want to write it into another table called 'SALES_ARCHIVE' Table to keep as backup.
So, I want to insert into 'SALES_ARCHIVE' Table from 'SALES' Table as soon as new transaction is arrived into 'SALES' Table.
BUT....at the moment, 'SALES' table is locked becasue of my trigger. It is locked for new trasaction while my trigger is running.
😀 I hope that explained about my problem.
Thanks.
Leo
September 10, 2008 at 9:30 am
A trigger's not the best way of doing archiving. Rather consider having a job that runs once a day (during a quiet time) or once a week or however often is necessary that moves data from the live sales table to the sales archive table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 10, 2008 at 9:33 am
Sorry to say this, but your explanation doesn't really jive with the code in your trigger from what I see.
As I said earlier, we need to see the DDL for the tables involved, some sample data for the tables, and what the expected results are to really be able to help.
If the Sales table and the SalesArchive table are physically the same, it should be easy to rewrite the trigger to accomplish what you want.
😎
September 10, 2008 at 9:47 am
AND, I actually have to agree with Gail. If this is strictly for archiving data, the trigger is not the best method.
😎
September 10, 2008 at 1:39 pm
Leo (9/10/2008)
First of all thaks to Lynn and Tim,Tim,
what do you mean 'locked table and not write to it, you can consider adding 'with (nolock)' to the reading transaction that is being blocked.'
You mean, I shouldn't use 'with (nolock)' option.
I mean that if you are only reading from the table that has the lock on it, you probably should use with (nolock). It will not help you if the command you are trying to do actually inserts/updates the table with the lock on it, but it can help with reads.
I have to emphasize again that using nolock can permit certain types of inconsistencies so you should only use it when you can afford the small chance of it happening.
But there are a great many cases where that is true and I make extensive use of nolock in my current project.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply