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
Thanks a lot. I am from Guatemala. Very useful information.
Hi:
Thanks for this! I have a situation and was searching for some help and landed here. I will try to explain it here in best possible way here. We use DB2 and have a stored procedure which is being called from Java application. Issue is we have a table where we find a duplicate record inserted. i.e, we have already data inserted for the criteria and it is soft deleted. Stored procedure bring it back (if data is already there and soft deleted) if we call it again for the same entry. In stored procedure, we are doing a select statement with UR and if we find a entry, we do an update statement. And immediately another select statement with UR is issued to read the updated values. If this query returns no data, an insert is made. And in all most all the cases, this worked without any issues. But we found 3-4 cases we see an insert is made even if entries are already present for the criteria. This is supposed not to happen. So, my question is can it be the case that second select didn’t get or “see” the updated values? Is it possible to happen in any case? Since it being called from java application commit will be when stored procedure call return to java code from DB. Is there such possibility that update shall be missed even if “with UR” clause is used? Thanks!
A merge statement may be a better choice than a select with a conditional insert.
If there is another application inserting that data, it is possible for you to not see it on the query with ur, and then see it on a future query with ur.
Really if you’re doing a select and then an insert of the same row, you should want them in the same uow, and should probably specifically NOT use with UR to prevent issues.