CODEWITHSUNDEEP
pythonoracle-database
D M
D M

Reputation: 180

should i do commit after every execute or after loop

I have 2000 inserts in a loop I do execute sql

My question is: should i do commit after every execute or after loop in order to minimise my affect on locking table and don't care about buffer?

the problem: my script sending to much queries and they stand in wait

I don't ask about limits of sql for x rows in commit and not about rollback in code. My question is about queue that standing half day in oracle server inactive and some waiting and prevent to new one proccess to run.

Upvotes: 1

Views: 2299

Answers (1)

Littlefoot
Littlefoot

Reputation: 142968

2000 inserts is close to nothing. Though, it would be far better if you could insert them at once, using a single INSERT statement, than doing it in a loop.

Generally speaking, commit once you're done with the transaction. It is most probably not ended at every turn of the loop, is it? Besides, committing in a loop (frequently) leads to ORA-01555 snapshot too old error.

Saying that "your script sends many queries" - what kind of them? SELECTs aren't blocked by anything. INSERTs aren't blocked either (I guess you don't lock the whole table, do you)? If you're trying to update rows locked by other user(s), that - obviously - won't work until they are released. The question is: why do those queries wait half a day? Smells like bad management.

Upvotes: 3

Related Questions