On December 12th, I’ll be presenting on the DB2 Night Show on HADR and TSAMP Advanced Topics. Sign up here: http://www.dbisoftware.com/db2nightshow/

Using “with ur” and The Trouble with GUIs

Every time I write a query for my own use, I tag “with ur” on the end of it. It is such a habit that the most common reason my “describe table” commands fail is because I added “with ur” on the end. I also teach my developers to use “with ur” any situation where they don’t know they need something else. When you use “with ur”, it acquires the fewest locks, and is least likely to interfere with the real workload on your e-commerce databases – the transactions.

What it Means

Using “with ur” on the end of a query tells DB2 that you want to use the Uncommitted Read isolation level. While Uncommitted Read is the least likely of all the isolation levels to acquire locks, it also can read data that has not been committed in the database. Usually this is not a problem as random queries that I or my developers are running don’t care about the concurrency phenomena that are observed with this isolation level. This is especially true if I’m querying something like completed orders by hour yesterday – I know that I’m not about to get another order sneaking in there today.

GUIs Suck

I am a notorious GUI-hater. I frequently give a newer version of the DB2 GUI(s) a chance. But it is not just for my own ease of use that I don’t like them. It is also because of what they do to my databases when someone else uses them. One of the reasons why I hate GUIs is that especially in development environments, you get users who don’t understand some of the database concepts using them to run SQL. Sometimes GUis don’t do auto-commit like the db2 CLP does, and so things the developers think applied to the database really didn’t. And other times they seem to run even a simple “select *” in Repeatable Read isolation level, allowing a user to lock things they didn’t intend to or don’t need.

What Can Go Wrong

This week, I got a request for help from developers, saying something was odd with the data. They said a row was there, but not there. Here’s how it looked:

]$ db2 "select * from wscomusr.srchattr where srchattr_id=-7000000000000000004
"

SRCHATTR_ID          INDEXSCOPE                                                                                                                       INDEXTYPE                                                                                                                        IDENTIFIER                                                                                                                                                                                                                                                     OPTCOUNTER
-------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------
-7000000000000000004 40000000579                                                                                                                      CatalogEntry                                                                                                                     _cat.Color                                                                                                                                                                                                                                                              0

  1 record(s) selected.

$ db2 "select * from wscomusr.srchattr where srchattr_id=-7000000000000000004
with ur"

SRCHATTR_ID          INDEXSCOPE                                                                                                                       INDEXTYPE                                                                                                                        IDENTIFIER                                                                                                                                                                                                                                                     OPTCOUNTER
-------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------

  0 record(s) selected.

Their delete appeared to them to do nothing, because it did not auto-commit. Then they tried to issue the same delete from the same session, and of course it couldn’t find a row to delete. I told them to try a

commit work

statement, but I don’t think they issued it in the right session. And when I tried the delete from a different session, I got a lock timeout. What I did to resolve the issue is I found their gui connection, forced it off, and did it from the command line where I knew it would succeed. The row was then “really” deleted, and the problem was solved. If they had done the delete from the command line, the issue would never have occurred.

Resources

Good tutorial on Concurrency and Isolation Levels (one of the most failed areas on the certification exams): http://www.ibm.com/developerworks/data/tutorials/db2-cert6106/index.html

0 comments… add one

Leave a Comment