User Trigger and Replication Deadlock Issue
-
Hello all. I posted this in TSQL, but am cross posting it here as well as it may have to do with replication.
We have a database with Merge replication to another subscribing server. Two of these tables require real-time updating of a summary table as transactions are posted to these tables.
The first table is an insert-only table, the second is insert/update. In both cases, I've written a trigger to perform the processing. In testing with using both Query Analyzer as well as Freetds on a Unix system (transaction data coming in from a PHP application via Freetds on a Solaris system w/Apache), all tests show that the triggers work, the summary table is updated, and everything is replicated.
The problem comes into play with the PHP application. As a test, I installed a trigger on just one table (the insert only table) to make sure it will work with existing PHP code. After allowing it to run for about 10 seconds, I found that we were getting deadlocks when the query executes. The insert query in the PHP code is bound in a transaction as are the the queries in the trigger.
The only thing I can see is possibly that when my trigger is added, the insert generates two result codes instead of the normal one (generated by the replication trigger). This is shown as after I remove the trigger, only one result code is returned. (All result codes were "0" - success).
Has anyone else had to deal with an issue such as this, or does anyone have some sage advice to deal with this? This is a critical operation that needs to be put into production, as soon as possible, and this little issue is holding things up. Hence, I turn to my collegues here
Thanks in advance for any assistance you can provide!
-- Joe
-
Your problem is indeed with Replication! When an insert takes place on a Table that is replicated that also has a trigger defined on it, 2 transactions will take place!
What you have to do is create the trigger with the NOT FOR REPLICATION clause. This will prevent the trigger firing on a replicated table! Have a look at BOL for the syntax and more info!
Are you using Microsoft SQL Server? You have mentioned UNIX & PHP, Microsoft SQL Server does not run on UNIX!
-
Ahhh - Not for replication! Got it (duh!) Gotta give that a try.
Yes, we're running SQL Server for the database. It's the web servers running Apache and PHP for development that run on the unix boxes. We use freetds to get to SQL Server. In rare occasions, we need to use Perl on unix as well, and for that we use DBD:Sybase, compiled using the freetds libraries and voila - a unix website with php and perl running with SQL Server as its database.
A lot of this is legacy stuff - the app used to use an open source db on unix - which turned out to be a maintenance headache, hence the switch to SQL Server. It's a first step in my underhanded way of getting rid of the "Microsoft is not for prime time" way of thinking around here
Thanks for the tip, now off to give it a go!
Cheers
-- Joe
-
Do yourself a favor and convince your manager that Sybase sucks a big fat one! Also tell him that Microsoft SQL Server has gone leaps and bounds and that they should give it a go before making such incompetent conclusions!
I have worked on Sybase and they don't have a descent tool to administer and develop in! So whats the point! By the end of this decade Sybase will not exist!
-
Hi - You might want to reread my previous reply. The only mention of Sybase is the use of DBD:Sybase for Perl, we do not use Sybase. Although I too, am not a fan of it - it shares several characteristics, most importantly its underlying transport protocol, TDS. Also, I am now "the manager"
We use SQL Server (7.0/2000) on Win2K/AS here, but if one needs to write a quick Perl script on un*x using a table or tables from a SQL Server database, then one must use DBD:Sybase, "compiled using the FreeTDS libraries" (for SQL Server).
Here's some URLs for those who are interested in looking at these tools or just want information.
FreeTDS (the TDS transport for Unix to SQL Server): http://www.freetds.org
DBD:Sybase (if compiled using FreeTDS libraries -- routine included -- use with SQL Server in Perl 5.x+): http://www.peppler.org
Both of these tools are a great value for the price -- they are excellent, and they are FREE OF CHARGE.
Cheers,
-- Joe
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply