Truncating Dates

Most developers using Oracle will know of the TRUNC function and how, when applied to a DATE data type, it removes (TRUNCates) any time component and sets it to “0”, which is midnight (also considered the start of the day):

ALTER SESSION SET nls_date_format = 'dd-Mon-yyyy hh24:mi:ss'
/

SELECT SYSDATE        AS date_time
,      TRUNC(SYSDATE) AS date_only
FROM   dual
/

which gives us:

DATE_TIME           DATE_ONLY
------------------- -------------------
03/07/2012 07:02:48 03/07/2012 00:00:00

What many developers don’t appear to know is that the TRUNC function accepts a second parameter, which specifies the level of precision to which the date is truncated. Of course, a casual read of the Oracle SQL documentation will reveal this but I guess without the knowledge that TRUNC has a second parameter most developers simply overlook it.

So, using a time of 4:15:32 pm on 12 June 2012, the listing below shows some of the different ways there are to apply the TRUNC function:

Precision Parameter Example
Year YY 01-JAN-2012 00:00:00
Quarter Q 01-APR-2012 00:00:00
Month MM 01-JUN-2012 00:00:00
Week DAY 10-JUN-2012 00:00:00
Day DD 12-JUN-2012 00:00:00
Hour HH 12-JUN-2012 16:00:00
Minute MI 12-JUN-2012 16:15:00

There are a few other TRUNC parameters available too. Naturally, there’s no option for truncating to the nearest second as that is the maximum level of precision of the DATE data type.

You may also note that the same options apply to the ROUND function.

Another potentially important point to keep in mind is that the TRUNC function operates on DATE data types. If you apply it to a TIMESTAMP data type then Oracle will implicitly convert your TIMESTAMP to a DATE, apply the TRUNC function and return a DATE data type. We can see this by using the DUMP function to display the underlying data type. You’ll note how in the following query, the data type is the same even though one was based on a TIMESTAMP and one on a DATE:

SELECT DUMP(TRUNC(SYSDATE))      AS dump_date
,      DUMP(TRUNC(SYSTIMESTAMP)) AS dump_timestamp
FROM   dual
/

DUMP_DATE                        DUMP_TIMESTAMP
-------------------------------- --------------------------------
Typ=13 Len=8: 220,7,7,3,0,0,0,0  Typ=13 Len=8: 220,7,7,3,0,0,0,0

You can view the Oracle documentation for a complete listing of the codes for each data type.

The implicit conversion and return data type are particularly important to understand as they are the places where bugs may be introduced. Drawing the difference between two TIMESTAMPs returns an INTERVAL data type but drawing the difference between two DATEs returns a NUMBER data type.

Advertisements

Locking and Blocking Basics

One of the most important aspects of designing a database application is to consider concurrency; multiple users or sessions simultaneously accessing and modifying the data. This is also one area that the various relational database platforms differ so it’s important to understand how it is handled in the one you work with. There are various aspects to concurrency and this post looks at the basics of row locking and blocking within Oracle.

Before we get started I’d like to mention a couple of points with regard to how Oracle works:

  • Locking is done at the row level. Yes, it’s possible to issue a LOCK TABLE statement but if you are simply modifying data then you will only be locking individual rows. There is no locking at the block or extent levels. Furthermore, Oracle does not escalate a series of row level locks to a table lock.
  • Oracle does not support dirty reads of data, which means that changes made by one session will only be visible to other sessions after a COMMIT has been issued. There is simply no facility in Oracle to perform a dirty read even if you wanted to do so.

The following examples all use the table and data defined below and were run under Oracle 11.2.0.3:

CREATE TABLE lock_test
  (id       NUMBER(10)    NOT NULL
  ,cat      NUMBER(2)     NOT NULL
  ,padding  CHAR(30)      NOT NULL)
/

INSERT INTO lock_test
  (id, cat, padding)
SELECT ROWNUM
,      MOD(ROWNUM,5)
,      'X'
FROM   dual
CONNECT BY level <= 10
/

COMMIT
/

ALTER TABLE lock_test
ADD CONSTRAINT lock_test_pk
PRIMARY KEY (id)
/  

Example 1
Let’s start with a simple test of a session updating one row whilst another session queries that same row before the update has been committed:

Session 1

SQL>UPDATE lock_test
  2  SET    cat = 99
  3  WHERE  id = 5
  4  /

1 row updated.

Session 2

SQL>SELECT *
  2  FROM   lock_test
  3  WHERE  id = 5
  4  /

        ID        CAT PADDING
---------- ---------- ------------------------------
         5          5 X

So, since the change in session 1 had not been committed session 2 does not see those changes and the value of the CAT column is 5 and not 99. However, from a locking and blocking perspective, whilst session 1 has modified the data and presumably taken out a lock on the row, session 2 was still able to query it, albeit to obain the pre-committed data. This example illustrates how sessions reading data are not blocked by sessions writing data in Oracle. This is a very important aspect of how locking and blocking works in Oracle; it is very difficult to block readers of data. This situation is what gives rise to the type of problem I described in my very first blog post.

Example 2
So, let’s see what would happen if session 2 tries to modiy the same row that session 1 is modifying:

Session 1

SQL>UPDATE lock_test
  2  SET    cat = 99
  3  WHERE  id = 5
  4  /

1 row updated.

Session 2

SQL>UPDATE lock_test
  2  SET    cat = 98
  3  WHERE  cat = 5
  4  /

Ummm… well, that’s not easy to show in a blog post. Anyway, session 2 appears to have simply stopped. It’s being blocked by the uncommitted transaction in session 1. Once we go into session 1 and issue a COMMIT (or a ROLLBACK for that matter) then session 2 reports:

1 row updated.

If we repeat the test, and while session 2 is blocked, we can see the details of the locks causing session 2 to wait via the data dictionary view DBA_WAITERS.

SELECT *
FROM   dba_waiters
WHERE  holding_session = SYS_CONTEXT('userenv','sid')
/

If we run this query in session 1, the session holding the lock, we get:

WAITING_SESSION HOLDING_SESSION LOCK_TYPE    MODE_HELD  MODE_REQUESTED   LOCK_ID1   LOCK_ID2
--------------- --------------- ------------ ---------- -------------- ---------- ----------
            131             200 Transaction  Exclusive  Exclusive          131095        982

So, session 1, which is SID 200, is holding an exclusive lock that is being requested by session 2, which is SID 131. We can see the details of the lock by querying v$lock:

SELECT *
FROM   v$lock
WHERE  id1 = 131095
AND    id2 = 982
/

which gives us the following rather cryptic details:

ADDR             KADDR              SID TY     ID1     ID2  LMODE  REQUEST  CTIME  BLOCK
---------------- ---------------- ----- -- ------- ------- ------ -------- ------ ------
000007FF3D8DAE98 000007FF3D8DAEF0   131 TX  131095     982      0        6    995      0
000007FF3BCE6938 000007FF3BCE69B0   200 TX  131095     982      6        0   1010      1

I don’t want to delve into the Oracle’s locking internals for this post so for now I’ll just note that the above view contains two rows, one for the holder of the lock (session 1) and one for the requester (session 2). The SIDs of the sessions are shown as is the time the lock has been held and whether the lock is blocking others. Very useful details when troubleshooting locking and blocking problems in a system.

A very important point to understand with how Oracle works is that session 2 will remain blocked until session 1 issues a commit or rollback. There is no automatic timeout. This feature in particular has significant application design implications. For example, you probably need to think very carefully when designing long running batch processes that will modify data which will operate concurrently with short OLTP-type operations. If your batch process obtains locks on rows that an OLTP session requires then you will be facing end-user complaints of bad performance!

Example 3
Back to our original tables. Let’s repeat the insert test again but alter the selection criteria of the row that is updated. Rather than modifying the row where the ID value is 5, we’ll modify the row where the CAT value is 5:

Session 1

SQL>UPDATE lock_test
  2  SET    cat = 99
  3  WHERE  cat = 5
  4  /

1 row updated.

Session 2

SQL>UPDATE lock_test
  2  SET    cat = 98
  3  WHERE  cat = 5
  4  /

Once again session 2 is blocked. If we commit the transaction in session 1 then session 2 reports:

0 rows updated.

Session 2 was blocked by session 1 as it wanted to update the same row based on the pre-existing, committed data. However, when session 1 committed its changes the row no longer satisfied the selection criteria for the update session 2 was performing so session 2 ended up modifing no rows.

What is interesting in this example was that session 2 initially attempted to obtain a lock on the row and was blocked by session 1. When session 1 released that lock, session 2 decided that it didn’t want to update the row since it no longer satisified the update criteria so it actually never needed to obtain any locks. Such are the complexities when dealing with concurrency… get used to it as it will be an important consideration in any multi-user database.

Example 4
So what would happen if session 2 tried to perform an update using a selection criteria that would include uncommitted rows from session 1? Let’s find out:

Session 1

SQL>UPDATE lock_test
  2  SET    cat = 99
  3  WHERE  cat = 5
  4  /

1 row updated.

Session 2

SQL>UPDATE lock_test
  2  SET    cat = 98
  3  WHERE  cat = 99
  4  /

0 rows updated.

Session 2 returned immediately and reported that it didn’t update any rows even though session 1 had an uncommitted change that would have satisified the selection criteria for session 2. Logically what happened was that session 2 read the data in the table, ignoring the uncommitted changes, and based its update on that view of the data… and that view did not have a row with a CAT value of 99. Since session 2 did not attempt to modify any rows that were locked by session 1 it was not blocked by session 1.

Example 5
Enough of updates, let’s take a look at inserts next. This time we’ll start off with 2 sessions inserting a single row into the table:

Session 1

SQL>INSERT INTO lock_test
  2    (id, cat, padding)
  3  VALUES
  4    (11, 99, 'X')
  5  /

1 row created.

Session 2:

SQL>INSERT INTO lock_test
  2    (id, cat, padding)
  3  VALUES
  4    (12, 98, 'X')
  5  /

1 row created.

Nothing unexpected here. Both sessions inserted a row without any problem since locking, and hence blocking, is done at the row level and not at a table, extent or block level.

Example 6
Let’s modify the example such that both attempt to insert the same set of values:

Session 1

SQL>INSERT INTO lock_test
  2    (id, cat, padding)
  3  VALUES
  4    (11, 99, 'X')
  5  /

1 row created.

Session 2:

SQL>INSERT INTO lock_test
  2    (id, cat, padding)
  3  VALUES
  4    (11, 99, 'X')
  5  /

Again, not easily llustrated in a blog post but session 2 is blocked by session 1. The reason? The uniqueness constraint on the table prevents multiple rows having the same ID value. So why has Oracle blocked session 2 and not simply aborted the operation? Session 1 hasn’t actually committed its insert so Oracle doesn’t know for certain if there is a problem. If we were to issue a commit in session 1 then session 2 would report:

INSERT INTO lock_test
*
ERROR at line 1:
ORA-00001: unique constraint (DEVELOPER.LOCK_TEST_PK) violated

However, if session 1 issued a rollback instead of a commit then session 2 would report:

1 row created.

As per our previous blocking examples, session 2 will remain blocked until session 1 performs a commit or rollback.

Example 7
Onto deletes then. The situation where two sessions both try to delete the same row probably causes no surprises:

Session 1

SQL>DELETE
  2  FROM   lock_test
  3  WHERE  id = 5
  4  /

1 row deleted.

Session 2

SQL>DELETE
  2  FROM   lock_test
  3  WHERE  id = 5
  4  /

Session 2 is blocked by session 1 as it’s trying to obtain a lock that session 1 already has. Once session 1 issues a commit then session 2 reports:

0 rows deleted.

Obviously session 1 has already deleted the row that session 2 was trying to delete so session 2 can’t delete it after all.

So, hopefully the above examples illustrates the basics of locking and blocking in Oracle, which are necessary features in any multi-user database system. These details are important to understand if you want to develop high performance systems. Time spent waiting whilst being blocked is essentially wasted time from an end user perspective so it’s important to minimise this.