locking and database link

Last week-end a new release went to production. This week we got plenty of ORA-2049. What in the hell went wrong? And why did not we find out this locking before?

We just did not have that concurrency effect in test.

How does locking work ?

With regular tables:
session 1
select * from lsc_t for update;
session 2
update lsc_t set id=3;

Session 2 waits until transaction in session 1 finishes.

But in case the update is using a subquery that access a remote table, the update will fail after 1 minute

session 1
select * from lsc_t for update;
session 2
update lsc_t set x=(select * from lsc_t@db02);
Session 2 will end with ORA-02049: timeout: distributed transaction waiting for lock

The timeout of one minute is defined by distributed_lock_timeout, and it is not modifiable in your session (chances are, you will not be able to modify this parameter).

1!=1

In the process of validating some calculation, I noticed some strange differences from one run to another run, with exactly the same data and exactly the same function.

I suppose the only difference was the order in which the transactions were processed (and since I had no ORDER BY, the order was not deterministic).

To limit it to the minimum, I noticed that, in Oracle, 1/3*3 is not 1 !


SQL> select * from dual where 1/3*3=1
no rows selected.

Let me tell you, this was unexpected. Ok, on my first pocket calculator 30 years ago, I noticed than 1/3=.3333333 and .3333333*3=.9999999. But since then, I used to expect 1.

perl :


$ perl -e 'print 1 if 1/3*3==1'
1

OK, I tried in java


public class X {
  public static void main(String[] args){
    if (1/3*3<1) {
      System.out.println("<\n");
    }
  }
}
$ javac X.java
$ java X
<

In java it seems different too.

So what is 1/3*3 in Oracle?


SQL> set numw 50
SQL> select 1/3*3 from dual;

                                             1/3*3
--------------------------------------------------
         .9999999999999999999999999999999999999999

Surprised?

package version control

Oracle does not offer any kind of version control like CVS or subversion in the database. CVS and subversion could be used in the filesystem, then the code could be deployed with sqlplus.

To quickly compare packages in Test and Production I used :


select 
  env,name,type,line,text 
from (
  select 
    env,name,type,line,text,
    count(distinct text) over(partition by name,type,line) c
  from (
    select 
      'TEST' env,name,type,line,text 
    from 
      user_source 
    union all
    select 
      'PROD' env,name,type,line,text 
    from 
      user_source@prod
  )
)
where c>1 ;

where “PROD” is a database link to production database. My security friends will scream to hear I have a database link to production in my test schema, but you are free to do it the other way round :mrgreen:

I also explained TOAD method in how to compare schema a few months ago.

to SQL or to PLSQL

Iggy Fernandez posted a riddle to be solved without PLSQL

http://www.amazon.com/gp/blog/post/PLNKI2MYB0YCYAUL

I tend to second Steven Feuerstein argument :
Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens.

In my book I mentionned techniques that enhance the basis functionality of SQL. Actually, many of those techniques allow you to write “shorter” statements than before (by avoid redundant subqueries) and also to gain performance, occasionnaly.

However the downside is that you will end up writting some code that only you can read.

In my opinion a zero-plsql approach is wrong. If you have a problem similar to Iggy Fernandez, then no no no, do not even try to solve it with SQL unless you are really doing this for fun

In my humble opinion, plsql and sql and complementary. If you want to program, use plsql. If you need to retrieve data, use sql.

And thanks a lot to Iggy for the excellent challenge !!!

ORA-2070

Today I received an error which was totally new to me. And totally unexpected…

For the purpose of this post, I reduced it to a minimum

CREATE TABLE LSC_T1
( x number);
CREATE TABLE LSC_T2
( x number);
with c as (select nls_charset_decl_len(1, x) 
  from lsc_t1 join lsc_t2 
  using (x))
select 1 from c
union 
select 1 from c;

Error at line 1
ORA-02070: database  does not support operator NLS_CHARSET_DECL_LEN in this context

My basic query was actually joining two dictionary views, and NLS_CHARSET_DECL_LEN is used in USER_TAB_COLUMNS.CHAR_COL_DECL_LENGTH.
Kind of

with c as (
  select table_name, column_name, constraint_name 
  from user_tab_columns 
  join user_constraints  
  using (table_name)
  where constraint_type='P'
)
select 
  constraint_name,column_name 
from c
union all
select 
  column_name,constraint_name 
from c;
Error at line 1
ORA-02070: database  does not support operator NLS_CHARSET_DECL_LEN in this context

I spent some time to understand the reason for getting an ORA-2070 in a simple query that did seem to be totally unrelated to charset. The database used for this test is 9.2.0.8/Solaris.

It is quite easy to avoid the bug, for instance by not using the ANSI join ;)

When analyzing such a defect, my approach is to simplify the query as much as possible. Very often on forums you see users posting queries of more than 100 lines. Those users just expect the community to debug their code. Posting a tiny reproducible test case is key to receive a proper solution!

dynamic database link

How do I select thru a database link, where the database link is not fixed?


SQL> var db_link varchar2(255);
SQL> exec :db_link := 'DB02';
SQL> select x from LSC_T@:db_link where x>0;  
select x from LSC_T@:db_link where x>0;  
                    *
ERROR at line 1:
ORA-01729: database link name expected

My solution using dynamic SQL
Remote databases DB02


create table lsc_t(x number);
insert into lsc_t(x) values (2);
commit;

Remote databases DB03


create table lsc_t(x number);
insert into lsc_t(x) values (3);
commit;

Local database DB01

create or replace type lsc_t_o as object(x number);
/
create or replace type lsc_t_c as table of lsc_t_o;
/
create or replace function lsc_f(str varchar2) 
return lsc_t_c is  
  rc lsc_t_c;  
begin  
  execute immediate 
    'select lsc_t_o(x) from '
    || dbms_assert.qualified_sql_name(str) bulk collect into rc;  
  return rc;  
end;  
/

Note the DBMS_ASSERT function. The DBMS_ASSERT just enhances the security by prevent SQL injection

Ok, let’s try

SQL> var db_link varchar2(255);
SQL> exec :db_link := 'DB02';
SQL> select x from table(lsc_f('LSC_T@'
  ||:db_link)) where x>0;  
         X
----------
         2
SQL> exec :db_link := 'DB03';
SQL> select x from table(lsc_f('LSC_T@'||:db_link)) where x>0;  
         X
----------
         3

Seems to work :)