On 12/7/2010 1:22 PM, Justin Pitts wrote:
>>
>> Also, as a fair warning: mssql doesn't really care about transactions, but
>> PG really does. Make sure all your code is properly starting and commiting
>> transactions.
>>
>> -Andy
>
> I do not understand that statement. Can you explain it a bit better?
In mssql you can write code that connects to the db, fire off updates
and inserts, and then disconnects. I believe mssql will keep all your
changes, and the transaction stuff is done for you.
In PG the first statement you fire off (like an "insert into" for
example) will start a transaction. If you dont commit before you
disconnect that transaction will be rolled back. Even worse, if your
program does not commit, but keeps the connection to the db open, the
transaction will stay open too.
There are differences in the way mssql and pg do transactions. mssql
uses a transaction log and keeps current data in the table. In mssql if
you open a transaction and write a bunch of stuff, the table contains
that new stuff. Everyone can see it. (I think default transaction
isolation level is read commited). But if you set your isolation level
to something with repeatable read, then your program will block and have
to wait on every little change to the table. (or, probably page.. I
think mssql has page level locking?)
anyway, in PG, multiple versions of the same row are kept, and when you
open, and keep open a transaction, PG has to keep a version of the row
for every change that other people make. So a long lasting transaction
could create hundreds of versions of one row. Then when somebody goes
to select against that table, it has to scan not only the rows, but
every version of every row!
So my point is, in PG, use transactions as they were meant to be used,
as single atomic operations. Start, do some work, commit.
mssql made it easy to ignore transactions by doing it for you. Ignoring
transaction in PG will hurt you.
you can google MVCC and "postgres idle in transaction" for more.
-Andy