Path: csiph.com!fu-berlin.de!uni-berlin.de!not-for-mail From: Chris Angelico Newsgroups: comp.lang.python Subject: Re: Handling transactions in Python DBI module Date: Thu, 11 Feb 2016 16:38:07 +1100 Lines: 49 Message-ID: References: <92D3C964-0323-46EE-B770-B89E7E7E6D36@ravnalaska.net> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 X-Trace: news.uni-berlin.de I/Vd9UCwyc2Hx2y+bJzotAJQNT+ewdMIZawmE1bOdaSA== Return-Path: X-Original-To: python-list@python.org Delivered-To: python-list@mail.python.org X-Spam-Status: OK 0.000 X-Spam-Evidence: '*H*': 1.00; '*S*': 0.00; 'received:209.85.223': 0.03; 'subject:Python': 0.05; 'correct.': 0.07; 'important,': 0.07; 'cc:addr:python-list': 0.09; 'executes': 0.09; 'postgresql,': 0.09; 'raised,': 0.09; 'similar,': 0.09; 'subject:module': 0.09; 'python': 0.10; 'exception': 0.13; 'explicitly': 0.15; 'thu,': 0.15; "'begin": 0.16; '2016': 0.16; 'boundary,': 0.16; 'conn': 0.16; 'conn,': 0.16; 'from:addr:rosuav': 0.16; 'from:name:chris angelico': 0.16; 'postgres,': 0.16; 'reason.': 0.16; 'received:io': 0.16; 'received:psf.io': 0.16; 'released.': 0.16; 'selects': 0.16; 'skip:n 70': 0.16; 'stuff,': 0.16; 'stuff.': 0.16; 'wrote:': 0.16; 'library,': 0.18; 'cc:2**0': 0.20; 'cc:addr:python.org': 0.20; 'implicit': 0.22; 'code,': 0.23; 'select': 0.23; 'feb': 0.23; 'wrote': 0.23; 'header:In-Reply-To:1': 0.24; 'not.': 0.27; 'parameters': 0.27; 'message-id:@mail.gmail.com': 0.27; 'specify': 0.27; "skip:' 10": 0.28; 'interface': 0.29; 'db2': 0.29; 'locks': 0.29; 'weak': 0.29; 'starts': 0.29; "we're": 0.30; 'work.': 0.30; 'creating': 0.30; 'transaction': 0.30; 'query': 0.30; 'strongly': 0.30; 'lock': 0.33; 'true.': 0.33; 'add': 0.34; 'gets': 0.35; 'received:google.com': 0.35; 'next': 0.35; 'level': 0.35; 'but': 0.36; 'should': 0.36; 'instead': 0.36; 'needed': 0.36; 'there': 0.36; 'received:209.85': 0.36; '(and': 0.36; 'basic': 0.36; 'pm,': 0.36; 'subject:: ': 0.37; 'received:209': 0.38; 'skip:p 20': 0.38; 'does': 0.39; "didn't": 0.39; 'some': 0.40; 'forget': 0.60; 'your': 0.60; 'back': 0.62; 'more': 0.63; 'strictly': 0.64; 'was:': 0.66; 'frank': 0.72; 'chrisa': 0.84; 'one).': 0.84; 'to:none': 0.91; 'acquires': 0.91; 'seriously,': 0.91 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=H0N2U30rg4t8dYU0gWly98yM5Cjfk8qgkt6U/ps/e0U=; b=dWLqxEFgEChn8RKBLF8onNuTqZkv/aAhwrF1NAqTnp8mJXk8Z5JWJGTbFBxmPauS+b k5MACuz0tbCvwaqKdru5dFF2TUOQgN2yreSfDMV3eOki2w81Jhh3D2lp0bedwAsLJQqC Kddyem9zqquiBu2ZjuPq4zTyrWXcGxoUCItT/ACWrq40QPKjRe3Ka7/oalgJu/Gr+x7c q+ZRTE2I2djaB5QvskjNP2+eIgY6+COE7VqfzY3uRQn1f00CkvBwVH5oHLMRMO1Jc8OC ZSr9EVVDYtvOPovlLImdSko/4+N3nLIxINl6F5upmvdDO2cBjjUTcWKqzl1CeQlUG9b7 ooPQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:cc:content-type; bh=H0N2U30rg4t8dYU0gWly98yM5Cjfk8qgkt6U/ps/e0U=; b=F5OLft/UjNBPGMWa+k4+cqy5HZCaK2IMNm3aYqp9QyZurdTMYP1lW3WbMozKx67/0g cqOUlD7zhvW4pztrOnAI8L7NjmY3GtTxuoESQ8cXhxLs3pdXSESpeolbFbOcEY46FBRp OPgCNjS5o6uiWi+cYVlA2FKdqLgDIiQxcEuIHxQZ7bXquOdlx4+LAYSt/RKeoSP802Be BBuqMorNnuP8KPaRQm3VgIrsQ5rI5HUZMzRrPgxuf77YY0zS2FQgEq/u9ZX+RHX1gFTs Ehm073AXYxd9O8EyYBt0lF3gzdi4uJTV1eIk510mnKGXzeO33xJcw2EIPRs++uls4WIs oRgQ== X-Gm-Message-State: AG10YOSL/1kIazRCo3fCFxmhGimZWJv5HVk9B7L57G+O/E5KQhLXZwcQg7hfcqMMBV9M1HxD1uGmilSosw9xMQ== X-Received: by 10.107.132.90 with SMTP id g87mr14443684iod.157.1455169087148; Wed, 10 Feb 2016 21:38:07 -0800 (PST) In-Reply-To: X-BeenThere: python-list@python.org X-Mailman-Version: 2.1.21rc2 Precedence: list List-Id: General discussion list for the Python programming language List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Xref: csiph.com comp.lang.python:102785 On Thu, Feb 11, 2016 at 4:28 PM, Frank Millman wrote: > "Chris Angelico" wrote in message > news:CAPTjJmphJvtCKUB6Qr-vp_1epEWxBgQxmfKEPMOhQp3pAPGG+A@mail.gmail.com... >> >> >> When I advise my students on basic databasing concepts, I recommend >> this structure: >> >> conn = psycopg2.connect(...) >> >> with conn, conn.cursor() as cur: >> cur.execute(...) >> > > Does this automatically issue a 'conn.commit()' on exit? If there was no exception raised, yes. If an exception starts to bubble across that boundary, the transaction's rolled back instead (and then the exception continues). > I have found that this is important, for the following reason. > > If you issue a bare SELECT to PostgreSQL, it executes it without creating > any locks. However, if it is inside a transaction, it does create a lock (I > forget exactly which one). That's not strictly true. A SELECT query always acquires some locks, albeit weak ones; but if you're not currently inside a transaction, you get an implicit transaction that gets immediately ended. So by the time you get to the next line of Python code, the locks have been released. > Because psycopg2 silently executes 'BEGIN TRANSACTION', your SELECTs always > happen inside a transaction whether you specify it or not. If you do not > issue a conn.commit(), the locks do not get cleared. Correct. And that's how you should _always_ work. When I started databasing seriously, it was with DB2 on OS/2, and if you didn't explicitly BEGIN TRANSACTION, one would be begun - and not ended. So the model was: Do your stuff, then commit/rollback. Then do more stuff. I never used BEGIN TRANSACTION unless I needed to add parameters to it (picking an isolation level or something). With Postgres, it's pretty similar, only you execute BEGIN TRANSACTION more explicitly. But that can be buried in the interface library, giving you a nice tidy system of "here, this is how we do some work... and now we're done". Which I strongly recommend. ChrisA