Path: csiph.com!usenet.pasdenom.info!news.franciliens.net!news.muarf.org!nntpfeed.proxad.net!proxad.net!feeder1-1.proxad.net!hq-usenetpeers.eweka.nl!hq-usenetpeers.eweka.nl!bcyclone05.am1.xlned.com!bcyclone05.am1.xlned.com!newsfeed.xs4all.nl!newsfeed2.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail Return-Path: X-Original-To: python-list@python.org Delivered-To: python-list@mail.python.org X-Spam-Status: OK 0.002 X-Spam-Evidence: '*H*': 1.00; '*S*': 0.00; 'anyway.': 0.05; 'explicitly': 0.05; 'back-end': 0.09; 'cursor': 0.09; 'definition,': 0.09; 'exit': 0.09; 'happens.': 0.09; 'must,': 0.09; 'cc:addr:python-list': 0.11; 'python': 0.11; 'assume': 0.14; '(when': 0.16; '90s,': 0.16; 'backward': 0.16; 'db2,': 0.16; 'from:addr:rosuav': 0.16; 'from:name:chris angelico': 0.16; 'numbers;': 0.16; 're-read': 0.16; 'wrote:': 0.18; 'thu,': 0.19; 'select': 0.22; 'cc:addr:python.org': 0.22; 'specifies': 0.24; 'cc:2**0': 0.24; 'header:In-Reply-To:1': 0.27; 'statement': 0.30; 'message-id:@mail.gmail.com': 0.30; 'controlled': 0.31; 'probably': 0.32; 'open': 0.33; 'transaction': 0.35; 'done.': 0.35; 'but': 0.35; 'received:google.com': 0.35; 'there': 0.35; 'example,': 0.37; 'nov': 0.38; 'pm,': 0.38; 'anything': 0.39; 'release': 0.40; 'how': 0.40; 'even': 0.60; 'easy': 0.60; 'transaction.': 0.60; 'back': 0.62; 'such': 0.63; 'between': 0.67; 'close': 0.67; 'frank': 0.68; 'default': 0.69; 'to:none': 0.92 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:cc :content-type; bh=QdpMRJmcs+VkMF0NyTrfNYhsYrTxuir1UG/qz5I1cP0=; b=dtgn2MDJAkZylIaOZiXJLdWRMv4BpVkCgS6PrHnWpNyJfo9WK/s9rQb6J7GVTQacKC zfOPusjaAjsMa9v6a2XDcTh5BKwQgnQ8LltcQWsQTDvZjbikduGdGi4UAnZ0/B2eF49J V8rDe3JsNsWbC3UEHeaPlia03lxRYc9Z5YEfWdBBL+swsNKuF0juMcloGS1nfzwSv/iP M+vNwroUJINqP83G5vECLRIeBS3eGrBrzz67gUCuhlgXls+UOnf9NPpQzTiZKJl5B843 uejIAumNwoaLyKhagKD6dDPjtK8fZHhlqF8TaEPUTXgO0MA5qP7Bi67GFxd0V1zXytwx FKrw== MIME-Version: 1.0 X-Received: by 10.50.23.48 with SMTP id j16mr17747573igf.34.1417084443344; Thu, 27 Nov 2014 02:34:03 -0800 (PST) In-Reply-To: References: Date: Thu, 27 Nov 2014 21:34:03 +1100 Subject: Re: Db transactions and locking From: Chris Angelico Cc: "python-list@python.org" Content-Type: text/plain; charset=UTF-8 X-BeenThere: python-list@python.org X-Mailman-Version: 2.1.15 Precedence: list List-Id: General discussion list for the Python programming language List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Newsgroups: comp.lang.python Message-ID: Lines: 27 NNTP-Posting-Host: 2001:888:2000:d::a6 X-Trace: 1417084446 news.xs4all.nl 2867 [2001:888:2000:d::a6]:40232 X-Complaints-To: abuse@xs4all.nl X-Received-Body-CRC: 516177367 X-Received-Bytes: 4531 Xref: csiph.com comp.lang.python:81623 On Thu, Nov 27, 2014 at 9:24 PM, Frank Millman wrote: > "PostgreSQL by default commits between each statement unless you explicitly > start a transaction." > > All Python database adaptors that I have used start a transaction when you > open a cursor. I have just re-read DB-API 2.0, and I cannot see anything > that specifies this behaviour, but AFAICT this is what happens. The default is probably for backward compatibility, but automatically opening a transaction is the better way. (When I used DB2, back in the 90s, that was the only way anything was ever done. There was no such thing as autocommit.) Assume that everything you do is inside a transaction; that's how it's going to be on the back-end anyway. For example, PostgreSQL's MVCC is controlled by transaction numbers; any row with a transaction number greater than the viewing transaction's is invisible. That means that even a SELECT statement must, by definition, be in a transaction. One easy way to use psycopg2 - though I don't know if this is the best way - is: with conn, conn.cursor() as cur: cur.execute(...) Exit the with block and you close off the transaction and release cursor resources. ChrisA