Транзакции для доступа DB только для чтения?
Об этом спрашивали раньше и отвечали здесь на стеке панды группового сортировки внутри групп . Что вам нужно сделать, так это создать фрейм группировки данных и создать столбец с суммой агрегации. Теперь создайте вторую группу в новом столбце агрегации. Затем используйте .nlargest, как упомянуто в посте.
3 ответа
Steven Devijver provided some good reasons for starting transactions even if the operations are only going read the database:
- Set timeouts or lock modes
- Set isolation level
Standard SQL requires that even a query must start a new transaction if there is no transaction currently in progress. There are DBMS where that is not what happens - those with an autocommit mode, for example (the statement starts a transaction and commits it immediately the statement completes). Other DBMS make statements atomic (effectively autocommit) by default, but start an explicit transaction with a statement such as 'BEGIN WORK', cancelling autocommit until the next COMMIT or ROLLBACK (IBM Informix Dynamic Server is one such - when the database is not MODE ANSI).
I'm not sure about the advice never to rollback. It makes no difference to the read-only transaction, and to the extent it annoys your DBAs, then it is better to avoid ROLLBACK. But if your program exits without doing a COMMIT, the DBMS should do a ROLLBACK on your incomplete transaction - certainly if it modified the database, and (for simplicity) even if you only selected data.
Overall, if you want to change the default behaviour of a series of operations, use a transaction, even if the transaction is read-only. If you are satisfied with the default behaviour, then it is not crucial to use a transaction. If your code is to be portable between DBMS, it is best to assume that you will need a transaction.
First off, this sounds like a premature optimization. As Steven pointed out, most sane databases are going to put you into a transaction anyway, and all they're really doing is calling commit after each statement. So from that perspective, autocommit might be less performant since each statement has to start a new transaction. Or maybe not. Only benchmarking will tell and I bet it doesn't make one lick of difference to your application.
One reason why you want to always use a transaction is consistency of protection. If you start fiddling with manually declaring a transaction only when you "need" then then you're going to forget at a critical time. Or that supposedly read-only set of operations suddenly isn't, either because a later programmer didn't realize it was supposed to be or because your code calls a function which has a hidden write. For example, I configure my command line database clients not to autocommit. This means I can fat finger a delete query and still rollback.
There's the isolation level, as pointed out. This allows you to do several reads without worrying if some other process has written to your data in between them making your reads effectively atomic. This will save you from many an hour debugging a race condition.
And, finally, you can often set a transaction to be read-only. This checks your assumption and will error out if something tries to write.
Here's a nice article summing it all up. The details are Oracle specific, but the concepts are generic.
Транзакция требуется для Операции только для чтения, если вы хотите установить конкретное время ожидания для запросов, отличных от времени ожидания по умолчанию, или если вы хотите изменить уровень изоляции.
Кроме того, каждая база данных - не знает об исключениях - будет внутренне запускать транзакцию для каждого запроса. Обычно считается, что откат транзакций не выполняется, когда откат не требуется.
Администраторы баз данных могут отслеживать активность отката, и любое поведение отката по умолчанию будет раздражать их.
Таким образом, транзакции используются в любом случае независимо от того, запускаете ли вы их или не. Если они вам не нужны, не запускайте их, но никогда не выполняйте откат операций только для чтения.