Saturday, September 13, 2014

Does SELECT Statement Start A Transaction In Oracle

Normal SELECT query doesn't start a transaction in Oracle. Oracle will start a transaction implicitly when it acquires a Transaction Lock for the first time. And TX lock is only obtained when you are trying to modify data. So a SELECT query like below which just retrieves data doesn't start a transaction:

select id from test_table where id=1;

But if you are using SELECT ... FOR UPDATE statement, that's a different scenario. SELECT ... FOR UPDATE will lock the selected rows so that you can modify them later and no other session can update the rows in the meantime. So in the below case, Oracle will start a new transaction:

select id from test_table where id=1 for update nowait;

Hope this clear up the things.

No comments:

Post a Comment