Tag Archives: Databases

Returning auto generated id in spring – mybatis

Faced a problem with returning the autogenerated id for new rows being created for a postgres table through spring – mybatis.

@Insert("insert into user (name) values (#{name})")
public void insertActor(User user);

One solution ofcourse was to simply get nextval from the sequence, but I wanted something cleaner where I need not be worried about multithreading issues.

Solution one Tried: One good solution found for postgres was using “Returning” keyword with Insert statement

insert into user (name) values (#{name}) Returning id;

Somehow that did not work with mybatis.

Solution that worked:

@Options(useGeneratedKeys=true, keyProperty = "userId", keyColumn="id")
@Insert("insert into user (name) values (#{name})")
public void insertActor(User user);

@Options, with useGeneratedKeys flag worked just fine. keyProperty defined the property name in Java and keyColumn name defined name of column in table.

Postgres error: Relation Does not exist

Faced an interesting error while accessing postgres table with a simple java code.

"select * from User"

A little googling revealed that if you are using multicase table name (U is caps), you will need to give the table name in quotes.

http://stackoverflow.com/questions/695289/cannot-simply-use-postgresql-table-name-relation-does-not-exist

A better fix would be to always use lower case characters when declaring tables in postgres.

Forgot password for Oracle accounts

I have a oracle installation on my windows machine but that was done a few months back and I could not recollect passwords for default sys and sysadmin records. The following trick helped reset passwords for these accounts

1. Go to Command prompt and type sqlplus /nolog. It should open SQL/> prompt.
2. On SQL/> prompt, type connect /as dba
Now you are logged in as dba, changing password is simple matter of
SQL> alter user sys identified by “newpassword”

Union vs Union All

The simple difference between using Union and Union All is that Union selects only distinct records from both the resultsets, whereas Union All will return everything.

But today I figured out one more use of union All today. In a ms sql query, I was using union to add up resultset of multiple queries, and got this error

“The text data type cannot be selected as DISTINCT because it is not comparable.”

As I knew none of my queries will return duplicate records, I did not need distinct clause. Replacing Union with “Union All” did the trick in this case.

Shared lock- Effecting Select query’s performance

A shared log in SQL query is applied by default by SELECT statements. The difference between Shared and exclusive locks is that exclusive lock will stop any update on the data being locked, whereas shared lock suggests the select query to wait if some update or insert operation is in progress for the row.

Shared lock will not stop any other request from selecting or updating the records.

For example- select * from employee;

The above statement will fetch employee data from employee table, but if at the same time some insert or update query is executing, it will impact the performance of select query as it has to wait for other operation (on the row/ page it is trying to read) to finish.

Workaround: If we are sure that other operations executing at the same time are not going to impact my select query, i.e. I am ok to read a few old records/ dirty read, Sql provides us keyword NOLOCK

select * from employee with (NOLOCK);

This will make sure our select query does not get impacted by insert and updates running at the same time, though we might have problem of dirty read.

Workaround 2: With NOLOCK we know we can have a dirty read problem, to avoid that, we can use READPAST. This will make the select query skip any rows being updated at the query time and hence avoids dirty reads, So we are achieving our goal of not waiting for insert/ update operations to be over plus. On the downside, the resultset will miss some of the records.

select * from employee with (READPAST);

Why rownum will nor work for greater or equal to checks

Someone asked a question on a forum that why rownum=2 does not work for his query, so i though of sharing some details here.

Using rownum is a tricky affair. Safest bet is to use it only when you want to limit the number of results to be shown. For example rownum<2 or rownum<=5.

Why rownum=2 or rownum>2 will not work?

Read here – http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

In summary, this is how oracle execute a query

  1. The FROM/WHERE clause goes first.
  2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
  3. SELECT is applied.
  4. GROUP BY is applied.
  5. HAVING is applied.
  6. ORDER BY is applied.

rownum<=2 clause will get converted to

ROWNUM = 1
for x in
( select * from emp )
loop
    exit when NOT(ROWNUM <= 2)
    OUTPUT record to temp
    ROWNUM = ROWNUM+1
end loop
SORT TEMP

if you change exit when NOT(ROWNUM <= 2) with rownnum=2, you can see it will fail in the first run itself

So if I cannot use rownum, what can I use. Try using row_number() http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm

It works something like

SELECT last_name FROM
   (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R FROM employees)
   WHERE R BETWEEN 51 and 100;

Isolation levels of a database

In my last post I talked about ACID properties of the database. Out of these, Isolation is one of the most important (and a bit complex) property as this makes sure that no “dirty-ready” occur. There can be various levels of database isolation.

1. Read Uncommitted: Lowest level of isolation, does not implement any checks, a transaction can read while other transaction is writing and hence dirty read can happen.

2. Read Committed. Reads only committed data. Handles dirty read problem. But say in same transaction, the data for a row in table is read multiple times, and if the data gets updated by some other transaction and committed, between the gap period of the two reads in this transaction, the transaction has read different data at different time intervals for same row.

e.g. select name from employee where id=10;

— do something which takes time

select name from employee where id=10;

commit;

The data can change between the two reads

3. Repeatable Reads: This solves the problem mentioned above with the Read Committed. That is, this level does not allow any modifications or deletion in the data which is being accessed by some other transaction. So in last example, if we read the same data multiple times, it is guaranteed to return the same info. Though we cannot update or delete the data in table being accessed by a transaction, we can still add more data. So if the query was

select name from employee;

instead of select name from employee where id=10;

we still could have different data set.

4. Serializable: This is the highest level of restriction added on transactions. This states, that no updation, deletion or addition can happen on a dataset/ table which is being accessed by another transaction. This will solve the problem which is mentioned in previous example. But the solution comes at a high cost as this restriction level will slow down the rate of transactions being executed.

ACID test for your database

ACID is a set of properties that each database system needs to guarantee in order to make sure that all the transactions will be processed reliably

Atomicity: A transaction is always committed as a full. That means if the transaction had 10 statements, all 10 are executed fully and committed. A partial execution of transaction will never occur.

Consistency: Database state is always stable. So if a transaction moves database from state A to B, both states are stable.

Isolation: All transactions will execute in isolation and do not interfere with one another (one transaction is updating the data and other is reading at the same time – dirty read is handled).

Durability: Once a transaction is committed, the changes will not be reverted by some hardware or software failure.