Wow... what a day! Locks and Devs....

So after working with one of our Java devs to sort out a beasty that goes by the name of STAGEPROP on a IBM WAS Commerce environment; I have come to a few conclusions.

1. DBA's and Devs need to spend time teaching each other.

2. Devs need to walk in a DBA's shoes.

3. Devs and DBA's need to understand Locking and its effects of database processes and impacts to the Data Model on your databases.

So what brought on this philosophical perlustration?

Let me start with some explanation of the facts and challenges. Stageprop is a process of loading data at scheduled times from a staging DB to the production DB for the OLTP Commerce environment. The purpose is to schedule updates, insert and deletes at a time that is less stressful to the production database. The purpose for this is to reduce the lock contentions, reduce the chance of loading bad data into the production DB for testing catalog data and shopping process commands before release to production. Since Commerce sites are up 24x7x365 this is a very useful utility. More information about stagprop can be found here http://tinyurl.com/2325nab .

So, why are we here? Well the stagprop that was running started listing referential integrity errors on the update and insert processes in the logs. Basically, When the stagprop tried to update the production DB the update/insert was failing on the stage prop tables due to triggers not firing after a update to the stage prop tables on the staglog table which controls the order of updates, data to be updated, and a record of the type, columns and values updated. The reason for the lock? Several different issues. No indices on the staglog table. While stagprop utility was running, other updates were being propagated to stagprop tables, (stagprop utility causes a table lock type of IX and X for row, we'll talk about this in a minute).

So this all boils down to shoddy performance, locks and all sorts of misfiring triggers. How did we find the issue? Well, that's for later along with how we fixed the misfired triggers and eliminated the issue from happing again! So, this post will be a 3-5 parter. Stay tuned for the rest!