Home Made Locks

I am working on some high priority problems my previous team is having. The customers are mad and want a fix yesterday. The applications keep aborting with the same error messages. I traced this down in the code to some object locking code.

This was Oracle Pro*C code that implements locks with records in a locks database table. When the users need to obtain a lock, they try to insert a record in this table. If anybody else has already inserted a similar record (has a lock), then the second lock immediately fails.

When the lock fails, the application displays an error message and aborts. This really irks our customer. So the first order of business is to make sure the application does not abort in this scenario. Next I figure we need the application to wait around a while until the lock might become free. why not let the Oracle database manage this contention? I figure somebody has already solved the queued lock problem. Why try to implement it again in our own code, especially when our implementation will be substandard.

Database Links

I need to create a lot of test data for unit testing some new code. Now I already had some good data in another database. So I used a tool to create some scripts that insert the data into the new database. There were some problems with this technique. My database tables have a lot of columns in them. The creation of insert scripts by the tool puts the whole insert statement on one line. This line turns out to be much too long for SQL*Plus to process. So I started to manually break up the lines. There had to be a better way.

Database links came to the rescue. You create the objects by specifying which database to connect to. You also need to provide login credentials when creating the link. Then you can perform queries that access the tables from the other database through the link. This was awesome. The link itself becomes an object in your schema. I hear you can even connect to non-Oracle database. I have no need for that feature though.

You do need to make sure the remote database you are linking to is in the tnsnames file on your server. Remember to do a COMMIT after you run the query through the link to release the rollback segment in the other database. The link operates like a pointer. If you really wanted to, a link can be used as a poor man's replication technology. If you want to get some information on your links, check out the DBA_DB_LINKS view.

Commitment Woes

I ran a bunch of routines in a stored procedures to generate some data. Then I queried that database from the SQL*Plus tool I previously used to create the data. The checks showed the new data was there. Finally I logged into my operating system account, and ran some shell scripts that ran my whole test suite.

The funny thing is that I could not see the results over in my SQL*Plus session. That was strange. The shell scripts call code that commit the changes. I had to take a break to analyze this oddity. Then it came to me. My SQL*Plus work had not been committed in the first place. Therefore the test suite in a separate session could not see the uncommitted changes. Doh!

If you thought that one was bad, listen to this story. I made some changes to the database. Then I called a stored procedure to run some jobs to process that data. Worked good the first time. Then I repeated the process. Changed up some data, called the exact stored procedure. This time the changes I made did not take. WTF? I even did a commit but got wrong results. A lot of inspection got me the answer. The stored procedure was in a package that initialized some internal variables. This package initialization happens once per session. My jobs were being run by one SQL*Plus session. I needed to log out and log back in to get the package initialization to rerun and process the new data. Ouch. That's a tricky one.