March 18, 2008 at 1:26 am
I have a table on which simultaneous updates can happen at the same time. I have written an after update trigger to do some processing of data.
But when multiple updates are getting fired at the same time, the first trigger runs successfully, but the rest of the triggers are aborted.
Can any one help me on how to make multiple updates run simultaneously on the same table at the same time?
Thanks in advance
March 19, 2008 at 3:26 am
what errors are you getting?
March 19, 2008 at 3:26 am
If you have multiple update triggers, be sure they don't overlap (functionally).
IOW, if you have two and they both update own table, one will fire the other one. In such case merge them into one or at least the update part.
You didn't mention what goes wrong nor what the triggers do.
From Jeff's signature:
For better, quicker answers, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 19, 2008 at 5:58 am
Hi Robert,
There is only one after update trigger on a table that contains production order details. The Quantity Produced column in this table will be updated from various shop floor locations (HMI). Do multiple triggers can be fired. There is no trigger getting called within a trigger.
Within the trigger we first save the production order number in a temporary table and then call a java program using xp_cmdshell for the Production order being updated and then do the confirmation in SAP for all the orders in the temporary table.
Now when we update 2 different production orders from different systems, the triggers get fired... but the result says that only the first triggers caused the successful execution of calling a java program. The other one didnt executed...
Error Displayed:
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
OR
Only the first trigger gets executed properly, the second doent execute
Following is the code that we have used in the trigger...
DECLARE
@orderNumber VARCHAR(14),
@cmd varchar(1000)
BEGIN
WAITFOR DELAY '00:00:01'
--Retrieve updated Quantity
SELECT @orderNumber=PARTSMANUFACTURING.M01_PRODUCTIONORDERNUMBER
FROM PARTSMANUFACTURING,INSERTED
WHERE PARTSMANUFACTURING.M01_PRODUCTIONORDERNUMBER=INSERTED.M01_PRODUCTIONORDERNUMBER;
--Insert Updated values in temporary table
BEGIN TRAN
INSERT INTO T_CONFIRMED_ORDERS VALUES(@orderNumber,GETDATE(),'SETConnector',GETDATE(),null,null,null);
COMMIT TRAN;
PRINT 'CALLING JAVA PROGRAM'
--call JAVA program
SET @cmd='java -cp C:\TCS\SETConnector\JavaTest;C:\TCS\SETConnector\SQLJDBC\sqljdbc.jar;C:\TCS\SETConnector\jco\jar\sapjco.jar; OrderConfirmation';
EXEC xp_cmdshell @cmd
END
March 19, 2008 at 6:09 am
Start by removing the BEGIN TRAN/COMMIT statements. They serve no use here because you have no error detection.
Check your Java program... is it keeping a file open?
Get rid of the WAIT FOR... it makes a long transaction even longer.
Last but not least... if someone inserts 1000 rows and two of them have the same order number, BOOM! Code will fail because you try to put the order number into a variable which can only accept one row. This isn't like Oracle where you write a single row trigger and tell it FOR EACH ROW... all the rows you inserted are in a single INSERTED table. You will need to rewrite this trigger to be set-based instead of RBAR.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 19, 2008 at 6:16 am
Not sure what you gain from the explicit transaction.
Can you pass the data you insert to the table to the java app as parameters? That may release you from having put any locks on the table in the Java app...
March 19, 2008 at 6:33 am
First of all, executing a java program from a trigger is a bad idea, because transactions must be short. Second, I'd guess the java program connects to database and performs some checks and updates, which is also a bad idea as it creates another transaction with its own locks. Third, java programs have its own problems, if not coded carefully.
It would be better if trigger performed all necessary checks. 😎
March 19, 2008 at 6:46 am
On second look it seems like everything is coded to slow down updates:
WAITFOR DELAY '00:00:01'
What is it for?
SELECT @orderNumber=PARTSMANUFACTURING.M01_PRODUCTIONORDERNUMBER
FROM PARTSMANUFACTURING,INSERTED
WHERE PARTSMANUFACTURING.M01_PRODUCTIONORDERNUMBER=INSERTED.M01_PRODUCTIONORDERNUMBER;
1. This works fine for single record update only.
2. why join to PARTSMANUFACTURING since all you fetch is M01_PRODUCTIONORDERNUMBER? It's the same as:
SELECT @orderNumber=M01_PRODUCTIONORDERNUMBER FROM INSERTED
After all this, trigger executes external java program without parameters, which presumably scans the whole temp table, so a single record update takes some seconds.
March 19, 2008 at 12:57 pm
I am going to guess that the [font="Courier New"]WAITFOR DELAY[/font] is only there to make testing this case easier (I have done this before) and is not normally there.
I am further going to guess that the Java programs are requerying the table in a way that is locking out the second INSERT or its trigger or its Java program. Somewhere in there the circuit close and a deadlock ensues.
I recommend what others here have said: 1) don't call a program from a trigger (use Service Broker to shunt this work off asynchronously if necessary) and 2) don't forget that SQL Server Triggers fire per-statement, NOT per-row as in other databases.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 19, 2008 at 10:40 pm
Hi All,
Thanks a lot for all these prompt replies....
Scenario:
----------
There are around 30 HMIs accross 3 production plants that will fire update comfirmation on PartsManufacturing Table which is centrally located in SQL Server. It is likely that multiple updates may happen at a single point of time.
Since this confimation needs to happen in a realtime basis, we have to have an event based solution. Hence we are calling a trigger. If there is any other way out to make this scene in realtime you can even suggest that.
I can think on the following lines now... Correct me if I am wrong
1) I can use a cursor in the trigger to get all the records from the Inserted table and populate the temporary table.
(Assuming that Inserted table has only records from Partsmanufacturing Table, which means there are separate Inserted tables being created for different tables--correct me if i am wrong)
2) May be use the Service Broker to call java program and process the temporary table.
One thing I forgot to mention is that afrer the record has been processed from the temporary table I am deleting that record.
The fields of temporary table are Production Order Number & a Time Stamp. So even if there are multiple updates on the same production order they will be saved with different Time Stamp and a new entry is created.
I have tried to work with Service Broker but didnt succeed on that part, may be you ppl can guide me how to go about...
I have even tried to do some R&D on SQL Server Jobs, but again when the second trigger fires, the first Job is still running so the Job started by second trigger is aborted. The delay in the trigger was added keeping this in mind that If the previous trigger is still executing, the other will wait for some seconds and then go ahead with the execution.
I have also tried to call a Java program by passing Order Number as a comman line argument within a trigger.... but somehow the program hangs out and the query status still displays executing... The same program from command prompt with same argument works perfectly fine and the output comes within 5-6 seconds.
I am new to SQL Server 2005 so may be the code that I have written is bad perfomance wise.
Please guide me with this trigger... I am working on it madly but have not yet come to a solution...
March 20, 2008 at 11:06 am
BEGIN TRAN
INSERT INTO T_CONFIRMED_ORDERS VALUES(@orderNumber,GETDATE(),'SETConnector',GETDATE(),null,null,null);
COMMIT TRAN;
Assuming (yes I know what that makes) that your table T_CONFIRMED has 7 columns might I suggest using the full form of the insert into statement (From BOL)
INSERT [INTO] table_or_view [(column_list)] data_values following these rules: When column_list does not specify all the columns in a table or view, either the default value, if a default is defined for the column, or NULL is inserted into any column that is not specified in the list. All columns that are not specified in the column list must either allow for null values or have a default value assigned.
Modify the T_CONFIRMED table and set the 2nd and 4th column's default value to GETDATE() and the 5th, 6th and 7th columns default values to NULL making less work for the trigger
and last but not least: from BOL
many resources acquired to support the Transact-SQL statements issued by the connection are locked by the transaction until it is completed with either a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement.
remove the TRANSACTION statements from the trigger. Also include the suggestions on modifying the select statement as made by others.
March 20, 2008 at 11:08 am
hitesh.p (3/19/2008)
Scenario:----------
There are around 30 HMIs accross 3 production plants that will fire update comfirmation on PartsManufacturing Table which is centrally located in SQL Server. It is likely that multiple updates may happen at a single point of time.
Since this confimation needs to happen in a realtime basis, we have to have an event based solution. Hence we are calling a trigger. If there is any other way out to make this scene in realtime you can even suggest that.
This sounds like a textbook case for Service Broker.
I can think on the following lines now... Correct me if I am wrong
1) I can use a cursor in the trigger to get all the records from the Inserted table and populate the temporary table.
Yes. Though you might be able to code around this for your T_* table, you would still need to do this for a Service Broker queue.
(Assuming that Inserted table has only records from Partsmanufacturing Table, which means there are separate Inserted tables being created for different tables--correct me if i am wrong)
Correct. The inserted table is really a pseudotable that is created local and specifc to each trigger activation.
2) May be use the Service Broker to call java program and process the temporary table.
Yes. Whether you can get to "real-time" (under 1-second response time guaranteed) will be entirely dependent on whether that Java program can keep up.
I have also tried to call a Java program by passing Order Number as a comman line argument within a trigger.... but somehow the program hangs out and the query status still displays executing...
This indicates that the Trigger and the Java program are locking-out each other. The best way to deal with this is to process these events Asynchronusly, as Service Broker does, instead of synchronusly as Triggers do.
The same program from command prompt with same argument works perfectly fine and the output comes within 5-6 seconds.
Ouch! That's not what "real-time" normally means. Your Java program may have real trouble keeping up with the work.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 20, 2008 at 3:45 pm
1) I can use a cursor in the trigger to get all the records from the Inserted table and populate the temporary table.
(Assuming that Inserted table has only records from Partsmanufacturing Table, which means there are separate Inserted tables being created for different tables--correct me if i am wrong)
I thought you wanted some speed in your triggers. Why would you use a cursor to do this?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply