Re: Transactions, cursors and timestamps
"Billy G. Allie" <[email protected]> Sat, 01 Apr 2006 15:26:44 -0500
| Newsgroups | gmane.comp.python.db.pypgsql.user |
|---|---|
| Message-ID | <[email protected]> |
--=-NF2qssnJ59MDjrIMJX6E
Content-Type: multipart/alternative; boundary="=-DF57tf7EfXdb+yTA14QO"
--=-DF57tf7EfXdb+yTA14QO
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable
On Mon, 2006-03-27 at 13:52 +0100, Terry Macdonald wrote:
> Hi,
>=20
> I don't now if I am implementing the DB-API via pypgsql properly but I=20
> have a web app that opens a db connection and a couple of cursors at=20
> application startup and I use the same cursors throughout the life of=20
> the app.
>=20
> Doing it this way I have noticed that transactions begin once a commit=20
> had been done. If an SQL statement acts on a table with a timestamp=20
> column using the now() function, as it is in a transaction the time=20
> used in the update/insert is when the transaction was started which=20
> means that after my last commit if a significant time has elapsed before=20
> the cursor is used again to update/insert a row the time stored is that=20
> of when the transaction began and not when the row was inserted which=20
> could easily be long after the transaction began.
>=20
> What is the best practice in using cursors and how do I get the=20
> timestamp to reflect when the row was created.
There are a number of things to consider:
1. Transactions are at the Connection level. This implies that all
cursors that share a connection share the transaction.
2. Transactions are started when the first cursor is opened on the
connection. Additional cursors created on the same connection
do no start another transaction (there is only one active
transaction per connection). If you need multiple transactions
at the same time, you will need multiple connections.
3. After a Connection.commit() or .rollback(), another transaction
is not started until a new cursor is created or until an query
is executed on an existing cursor. Remember - if there are
multiple cursors on the connection, the transaction is started
the first time .executeXXX or .callproc is executed on any one
of them.
Given these thing, I would recommend:
1. Using only one cursor per connection, unless you need to process
through the results of multiple queries simotaineously.
2. If you need to perform updates based on the results of
processing the results of a query in progress, use two
connections with 1 cursor each. One for the query being
processed, the other for the updates.
3. Commit the updates as soon as passible, preferrably after each
row if there is significant time between the updates. This will
delay the creation of a transaction until the next update
occurs, ensuring that the timestamps reflect when the row was
created.
4. If creating a cursor ahead of time, issue a connection.rollback
() after creating all the cursors for that connection. This
will delay the creation of the transaction until the first time
a cursor for that connection is used.
--=20
Billy G. Allie <[email protected]>
--=-DF57tf7EfXdb+yTA14QO
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; CHARSET=3DUTF-8">
<META NAME=3D"GENERATOR" CONTENT=3D"GtkHTML/3.3.2">
</HEAD>
<BODY>
On Mon, 2006-03-27 at 13:52 +0100, Terry Macdonald wrote:
<BLOCKQUOTE TYPE=3DCITE>
<PRE>
<FONT COLOR=3D"#000000">Hi,</FONT>
<FONT COLOR=3D"#000000">I don't now if I am implementing the DB-API via pyp=
gsql properly but I </FONT>
<FONT COLOR=3D"#000000">have a web app that opens a db connection and a cou=
ple of cursors at </FONT>
<FONT COLOR=3D"#000000">application startup and I use the same cursors thro=
ughout the life of </FONT>
<FONT COLOR=3D"#000000">the app.</FONT>
<FONT COLOR=3D"#000000">Doing it this way I have noticed that transactions =
begin once a commit </FONT>
<FONT COLOR=3D"#000000">had been done. If an SQL statement acts on a table =
with a timestamp </FONT>
<FONT COLOR=3D"#000000">column using the now() function, as it is in a tra=
nsaction the time </FONT>
<FONT COLOR=3D"#000000">used in the update/insert is when the transaction w=
as started which </FONT>
<FONT COLOR=3D"#000000">means that after my last commit if a significant ti=
me has elapsed before </FONT>
<FONT COLOR=3D"#000000">the cursor is used again to update/insert a row the=
time stored is that </FONT>
<FONT COLOR=3D"#000000">of when the transaction began and not when the row =
was inserted which </FONT>
<FONT COLOR=3D"#000000">could easily be long after the transaction began.</=
FONT>
<FONT COLOR=3D"#000000">What is the best practice in using cursors and how =
do I get the </FONT>
<FONT COLOR=3D"#000000">timestamp to reflect when the row was created.</FON=
T>
</PRE>
</BLOCKQUOTE>
There are a number of things to consider:
<OL TYPE=3D1>
<LI TYPE=3D1 VALUE=3D1>Transactions are at the Connection level. =
This implies that all cursors that share a connection share the transaction=
.
<LI TYPE=3D1 VALUE=3D2>Transactions are started when the first cursor i=
s opened on the connection. Additional cursors created on the same co=
nnection do no start another transaction (there is only one active transact=
ion per connection). If you need multiple transactions at the same ti=
me, you will need multiple connections.
<LI TYPE=3D1 VALUE=3D3>After a Connection.commit() or .rollback(), anot=
her transaction is not started until a new cursor is created or until an qu=
ery is executed on an existing cursor. <I>Remember - if there are mul=
tiple cursors on the connection, the transaction is started the first time =
.executeXXX or .callproc is executed on any one of them.</I>
</OL>
Given these thing, I would recommend:
<OL TYPE=3D1>
<LI TYPE=3D1 VALUE=3D1>Using only one cursor per connection, unless you=
need to process through the results of multiple queries simotaineously.
<LI TYPE=3D1 VALUE=3D2>If you need to perform updates based on the resu=
lts of processing the results of a query in progress, use two connections w=
ith 1 cursor each. One for the query being processed, the other for t=
he updates.
<LI TYPE=3D1 VALUE=3D3>Commit the updates as soon as passible, preferra=
bly after each row if there is significant time between the updates. =
This will delay the creation of a transaction until the next update occurs,=
ensuring that the timestamps reflect when the row was created.
<LI TYPE=3D1 VALUE=3D4>If creating a cursor ahead of time, issue a conn=
ection.rollback() after creating all the cursors for that connection. =
This will delay the creation of the transaction until the first time a cur=
sor for that connection is used.
</OL>
<BR>
<TABLE CELLSPACING=3D"0" CELLPADDING=3D"0" WIDTH=3D"100%">
<TR>
<TD>
-- <BR>
Billy G. Allie <<A HREF=3D"mailto:[email protected]">bill.allie=
@defiant.mug.org</A>>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>
--=-DF57tf7EfXdb+yTA14QO--
--=-NF2qssnJ59MDjrIMJX6E
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.7 (GNU/Linux)
iD8DBQBELuIEnmIkMXoVVdURAl5lAJsEvzOy3sl2cfNnMuQbdtl+vnhFiQCg7I8k
X6sPybinGIeLNLZSsBlUmHc=
=6nC+
-----END PGP SIGNATURE-----
--=-NF2qssnJ59MDjrIMJX6E--
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642