CODEWITHSUNDEEP
javajdbcoracle11gjta
user5157427
user5157427

Reputation: 253

Does a select SQL Statement on oracle lock the used table for update by other connections?

Does a select SQL Statement on oracle lock the used table for update by other connections? is it depend if I'm doing the select inside a jta transaction or not ? how can I control this issue and prevent from read only SQL Statements to lock tables for editing ?

Upvotes: 2

Views: 13742

Answers (2)

Just a Fella Now
Just a Fella Now

Reputation: 61

I know this is an old question, but I think it deserves a better answer. If you reference Automatic Locks in DML Operations, you will find that the only SELECT statements that will lock tables are those with the FOR UPDATE clause, and since "queries without the FOR UPDATE clause do not acquire any data locks to block other operations, such queries are often referred to as nonblocking queries".

Upvotes: 6

Jan
Jan

Reputation: 13858

It depends on a lot of stuff - like your transaction isolation setting for instance. Even in auto-commit mode you'd have some if rather short transactions on your db.

So yes, a SELECT can create a lock that others have to wait for.

If you want to prevent this from happening in large scale and know what you're doing, transaction isolation "read uncommitted" will create fewest locks (and offer least protection)

Upvotes: 2

Related Questions