Thread: Simple question on SELECT

Does simple SELECT query like

select * from tableX

(without FOR UPDATE) opens an implicit transaction when issued?


Thanks,

On 11/07/11 2:41 PM, Edson Richter wrote:
> Does simple SELECT query like
>
> select * from tableX
>
> (without FOR UPDATE) opens an implicit transaction when issued?

opens and closes.    if you don't bracket with BEGIN;  ....
COMMIT|ROLLBACK;    then each statement is a transaction of and by itself



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

Em 07-11-2011 20:54, John R Pierce escreveu:
> On 11/07/11 2:41 PM, Edson Richter wrote:
>> Does simple SELECT query like
>>
>> select * from tableX
>>
>> (without FOR UPDATE) opens an implicit transaction when issued?
>
> opens and closes.    if you don't bracket with BEGIN;  ....
> COMMIT|ROLLBACK;    then each statement is a transaction of and by itself

Thanks for the fast answer. Is there any way to avoid that? I mean, in
MS SQL Server, I do have "with no_lock" (that produces dirty reads)?
Or the way to go is the transaction isolation level?

Regards,

Edson.
>
>
>

On Mon, Nov 7, 2011 at 3:30 PM, Edson Richter <richter@simkorp.com.br> wrote:
> Thanks for the fast answer. Is there any way to avoid that? I mean, in MS
> SQL Server, I do have "with no_lock" (that produces dirty reads)?
> Or the way to go is the transaction isolation level?

The lowest level of Isolation supported by PostgreSQL is READ
COMMITTED which is the default transaction isolation level:
http://www.postgresql.org/docs/9.1/interactive/sql-set-transaction.html


--
Regards,
Richard Broersma Jr.

On 11/07/11 3:30 PM, Edson Richter wrote:
> Thanks for the fast answer. Is there any way to avoid that? I mean, in
> MS SQL Server, I do have "with no_lock" (that produces dirty reads)?
> Or the way to go is the transaction isolation level?

there's no actual overhead in a single statement read transaction like
this.   Postgres uses a novel approach to MVCC.



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast