[Checkins] SVN: relstorage/trunk/ Oracle: use a timeout on commit locks. This requires installation
Shane Hathaway
shane at hathawaymix.org
Tue Oct 27 03:17:27 EDT 2009
Log message for revision 105304:
Oracle: use a timeout on commit locks. This requires installation
of a small PL/SQL package that can access the DBMS_LOCK package.
Also added documentation on how to set up databases.
Changed:
U relstorage/trunk/CHANGES.txt
U relstorage/trunk/README.txt
A relstorage/trunk/buildout-oracle.cfg
U relstorage/trunk/relstorage/adapters/locker.py
U relstorage/trunk/relstorage/adapters/oracle.py
U relstorage/trunk/relstorage/tests/README.txt
-=-
Modified: relstorage/trunk/CHANGES.txt
===================================================================
--- relstorage/trunk/CHANGES.txt 2009-10-26 19:14:27 UTC (rev 105303)
+++ relstorage/trunk/CHANGES.txt 2009-10-27 07:17:27 UTC (rev 105304)
@@ -32,8 +32,11 @@
objects, making the adapter code more modular. Added interfaces
that describe the duties of each part.
-- Oracle: sped up restore operations by sending short blobs inline
+- Oracle: sped up restore operations by sending short blobs inline.
+- Oracle: use a timeout on commit locks. This requires installation
+ of a small PL/SQL package that can access DBMS_LOCK. See README.txt.
+
- PostgreSQL: use the documented ALTER SEQUENCE RESTART WITH
statement instead of ALTER SEQUENCE START WITH.
Modified: relstorage/trunk/README.txt
===================================================================
--- relstorage/trunk/README.txt 2009-10-26 19:14:27 UTC (rev 105303)
+++ relstorage/trunk/README.txt 2009-10-27 07:17:27 UTC (rev 105304)
@@ -49,6 +49,105 @@
itself only with certain character set configurations. MySQLdb 1.2.2 fixes the
bug.
+Configuring Your Database
+-------------------------
+
+You need to create a database, user name, and password for RelStorage.
+RelStorage will populate the database with its schema the first time it
+connects.
+
+PostgreSQL
+~~~~~~~~~~
+
+If you installed PostgreSQL from a binary package, you probably have a
+user account named ``postgres``. Since PostgreSQL respects the name of
+the logged-in user by default, switch to the ``postgres`` account to
+create the RelStorage user and database. Even ``root`` does not have
+the PostgreSQL privileges that the ``postgres`` account has. For
+example::
+
+ $ sudo su - postgres
+ $ createuser --pwprompt zodbuser
+ $ createdb -O zodbuser zodb
+
+New PostgreSQL accounts often require modifications to ``pg_hba.conf``,
+which contains host-based access control rules. PostgreSQL processes
+the rules in order, so add new rules before the default rules rather than
+after. Here is a sample rule that allows only local connections by
+``zodbuser`` to the ``zodb`` database::
+
+ local zodb zodbuser md5
+
+PostgreSQL re-reads ``pg_hba.conf`` when you ask it to reload its
+configuration file::
+
+ /etc/init.d/postgresql reload
+
+MySQL
+~~~~~
+
+Use the ``mysql`` utility to create the database and user account. One
+surprising behavior of the ``mysql`` utility is the ``-p`` option. You
+must use the ``-p`` option if the account you are accessing requires a
+password, but you should not use the ``-p`` option if the account you
+are accessing does not require a password. Most people must use the
+``-p`` option. If you do not provide the ``-p`` option, yet the account
+requires a password, the ``mysql`` utility will not prompt for a
+password and will fail to authenticate.
+
+Therefore, most users can start the ``mysql`` utility with the
+following shell command (unlike PostgreSQL, your login account name does
+not matter)::
+
+ $ mysql -u root -p
+
+Here are some sample SQL statements for creating the user and database::
+
+ CREATE USER 'zodbuser'@'localhost' IDENTIFIED BY 'mypassword';
+ CREATE DATABASE zodb;
+ GRANT ALL ON zodb.* TO 'zodbuser'@'localhost';
+ FLUSH PRIVILEGES;
+
+Oracle
+~~~~~~
+
+Initial setup will require ``SYS`` privileges. Using Oracle 10g XE, you
+can start a ``SYS`` session with the following shell commands::
+
+ $ su - oracle
+ $ sqlplus / as sysdba
+
+The commands below will create a PL/SQL package that provides limited
+access to the DBMS_LOCK package so that RelStorage can acquire user
+locks. Using ``sqlplus`` with ``SYS`` privileges, execute the
+following::
+
+ CREATE OR REPLACE PACKAGE relstorage_util AS
+ FUNCTION request_lock(id IN NUMBER, timeout IN NUMBER)
+ RETURN NUMBER;
+ END relstorage_util;
+ /
+
+ CREATE OR REPLACE PACKAGE BODY relstorage_util AS
+ FUNCTION request_lock(id IN NUMBER, timeout IN NUMBER)
+ RETURN NUMBER IS
+ BEGIN
+ RETURN DBMS_LOCK.REQUEST(
+ id => id,
+ lockmode => DBMS_LOCK.X_MODE,
+ timeout => timeout,
+ release_on_commit => TRUE);
+ END request_lock;
+ END relstorage_util;
+ /
+
+Here are some sample SQL statements for creating the user and database::
+
+ CREATE USER zodb IDENTIFIED BY mypassword;
+ GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE TO zodb;
+ GRANT EXECUTE ON relstorage_util TO zodb;
+
+
Configuring Plone
-----------------
Added: relstorage/trunk/buildout-oracle.cfg
===================================================================
--- relstorage/trunk/buildout-oracle.cfg (rev 0)
+++ relstorage/trunk/buildout-oracle.cfg 2009-10-27 07:17:27 UTC (rev 105304)
@@ -0,0 +1,19 @@
+[buildout]
+extends = buildout.cfg
+parts = cx_Oracle test python coverage-test coverage-report
+eggs += cx_Oracle
+oracle_home = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server
+
+[cx_Oracle]
+recipe = zc.recipe.egg:custom
+environment = oracle-env
+rpath = ${buildout:oracle_home}/lib
+
+[oracle-env]
+ORACLE_HOME = ${buildout:oracle_home}
+
+[test]
+environment = oracle-env
+
+[test-coverage]
+environment = oracle-env
Modified: relstorage/trunk/relstorage/adapters/locker.py
===================================================================
--- relstorage/trunk/relstorage/adapters/locker.py 2009-10-26 19:14:27 UTC (rev 105303)
+++ relstorage/trunk/relstorage/adapters/locker.py 2009-10-27 07:17:27 UTC (rev 105304)
@@ -127,10 +127,32 @@
class OracleLocker(Locker):
implements(ILocker)
+ def __init__(self, keep_history, lock_exceptions,
+ commit_lock_id, inputsize_NUMBER):
+ self.keep_history = keep_history
+ self.lock_exceptions = lock_exceptions
+ self.commit_lock_id = commit_lock_id
+ self.inputsize_NUMBER = inputsize_NUMBER
+
def hold_commit_lock(self, cursor, ensure_current=False):
# Hold commit_lock to prevent concurrent commits
# (for as short a time as possible).
- cursor.execute("LOCK TABLE commit_lock IN EXCLUSIVE MODE")
+ status = cursor.callfunc(
+ "sys.relstorage_util.request_lock",
+ self.inputsize_NUMBER,
+ (self.commit_lock_id, commit_lock_timeout))
+ if status != 0:
+ if status >= 1 and status <= 5:
+ msg = ('', 'timeout', 'deadlock', 'parameter error',
+ 'lock already owned', 'illegal handle')[int(status)]
+ else:
+ msg = str(status)
+ raise StorageError(
+ "Unable to acquire commit lock (%s)" % msg)
+
+ # Alternative:
+ #cursor.execute("LOCK TABLE commit_lock IN EXCLUSIVE MODE")
+
if ensure_current:
if self.keep_history:
# Lock transaction and current_object in share mode to ensure
Modified: relstorage/trunk/relstorage/adapters/oracle.py
===================================================================
--- relstorage/trunk/relstorage/adapters/oracle.py 2009-10-26 19:14:27 UTC (rev 105303)
+++ relstorage/trunk/relstorage/adapters/oracle.py 2009-10-27 07:17:27 UTC (rev 105304)
@@ -52,7 +52,8 @@
"""Oracle adapter for RelStorage."""
implements(IRelStorageAdapter)
- def __init__(self, user, password, dsn, twophase=False, options=None):
+ def __init__(self, user, password, dsn, commit_lock_id=0,
+ twophase=False, options=None):
"""Create an Oracle adapter.
The user, password, and dsn parameters are provided to cx_Oracle
@@ -84,6 +85,8 @@
self.locker = OracleLocker(
keep_history=self.keep_history,
lock_exceptions=(cx_Oracle.DatabaseError,),
+ commit_lock_id=commit_lock_id,
+ inputsize_NUMBER=cx_Oracle.NUMBER,
)
self.schema = OracleSchemaInstaller(
connmanager=self.connmanager,
Modified: relstorage/trunk/relstorage/tests/README.txt
===================================================================
--- relstorage/trunk/relstorage/tests/README.txt 2009-10-26 19:14:27 UTC (rev 105303)
+++ relstorage/trunk/relstorage/tests/README.txt 2009-10-27 07:17:27 UTC (rev 105304)
@@ -10,48 +10,87 @@
PostgreSQL
----------
-CREATE USER relstoragetest WITH PASSWORD 'relstoragetest';
-CREATE DATABASE relstoragetest OWNER relstoragetest;
-CREATE DATABASE relstoragetest2 OWNER relstoragetest;
-CREATE DATABASE relstoragetest_hf OWNER relstoragetest;
-CREATE DATABASE relstoragetest2_hf OWNER relstoragetest;
+Execute the following using the ``psql`` command::
+ CREATE USER relstoragetest WITH PASSWORD 'relstoragetest';
+ CREATE DATABASE relstoragetest OWNER relstoragetest;
+ CREATE DATABASE relstoragetest2 OWNER relstoragetest;
+ CREATE DATABASE relstoragetest_hf OWNER relstoragetest;
+ CREATE DATABASE relstoragetest2_hf OWNER relstoragetest;
+
Also, add the following lines to the top of pg_hba.conf (if you put
-them at the bottom, they may be overridden by other parameters):
+them at the bottom, they may be overridden by other parameters)::
-local relstoragetest relstoragetest md5
-local relstoragetest2 relstoragetest md5
-local relstoragetest_hf relstoragetest md5
-local relstoragetest2_hf relstoragetest md5
+ local relstoragetest relstoragetest md5
+ local relstoragetest2 relstoragetest md5
+ local relstoragetest_hf relstoragetest md5
+ local relstoragetest2_hf relstoragetest md5
MySQL
-----
-CREATE USER 'relstoragetest'@'localhost' IDENTIFIED BY 'relstoragetest';
-CREATE DATABASE relstoragetest;
-GRANT ALL ON relstoragetest.* TO 'relstoragetest'@'localhost';
-CREATE DATABASE relstoragetest2;
-GRANT ALL ON relstoragetest2.* TO 'relstoragetest'@'localhost';
-CREATE DATABASE relstoragetest_hf;
-GRANT ALL ON relstoragetest_hf.* TO 'relstoragetest'@'localhost';
-CREATE DATABASE relstoragetest2_hf;
-GRANT ALL ON relstoragetest2_hf.* TO 'relstoragetest'@'localhost';
-FLUSH PRIVILEGES;
+Execute the following using the ``mysql`` command::
+ CREATE USER 'relstoragetest'@'localhost' IDENTIFIED BY 'relstoragetest';
+ CREATE DATABASE relstoragetest;
+ GRANT ALL ON relstoragetest.* TO 'relstoragetest'@'localhost';
+ CREATE DATABASE relstoragetest2;
+ GRANT ALL ON relstoragetest2.* TO 'relstoragetest'@'localhost';
+ CREATE DATABASE relstoragetest_hf;
+ GRANT ALL ON relstoragetest_hf.* TO 'relstoragetest'@'localhost';
+ CREATE DATABASE relstoragetest2_hf;
+ GRANT ALL ON relstoragetest2_hf.* TO 'relstoragetest'@'localhost';
+ FLUSH PRIVILEGES;
+
Oracle
------
-Execute these commands as the 'SYSTEM' user. When running the tests,
-you can use the environment variable ORACLE_TEST_DSN to override the
-data source name, which defaults to "XE" (for Oracle 10g XE).
+Initial setup will require ``SYS`` privileges. Using Oracle 10g XE, you
+can start a ``SYS`` session with the following shell commands::
-CREATE USER relstoragetest IDENTIFIED BY relstoragetest;
-GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE TO relstoragetest;
-CREATE USER relstoragetest2 IDENTIFIED BY relstoragetest;
-GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE TO relstoragetest2;
-CREATE USER relstoragetest_hf IDENTIFIED BY relstoragetest;
-GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE TO relstoragetest_hf;
-CREATE USER relstoragetest2_hf IDENTIFIED BY relstoragetest;
-GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE TO relstoragetest2_hf;
+ $ su - oracle
+ $ sqlplus / as sysdba
+
+The commands below will create a PL/SQL package that provides limited
+access to the DBMS_LOCK package so that RelStorage can acquire user
+locks. Using ``sqlplus`` with ``SYS`` privileges, execute the
+following::
+
+ CREATE OR REPLACE PACKAGE relstorage_util AS
+ FUNCTION request_lock(id IN NUMBER, timeout IN NUMBER)
+ RETURN NUMBER;
+ END relstorage_util;
+ /
+
+ CREATE OR REPLACE PACKAGE BODY relstorage_util AS
+ FUNCTION request_lock(id IN NUMBER, timeout IN NUMBER)
+ RETURN NUMBER IS
+ BEGIN
+ RETURN DBMS_LOCK.REQUEST(
+ id => id,
+ lockmode => DBMS_LOCK.X_MODE,
+ timeout => timeout,
+ release_on_commit => TRUE);
+ END request_lock;
+ END relstorage_util;
+ /
+
+ CREATE USER relstoragetest IDENTIFIED BY relstoragetest;
+ GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE TO relstoragetest;
+ GRANT EXECUTE ON relstorage_util TO relstoragetest;
+ CREATE USER relstoragetest2 IDENTIFIED BY relstoragetest;
+ GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE TO relstoragetest2;
+ GRANT EXECUTE ON relstorage_util TO relstoragetest2;
+ CREATE USER relstoragetest_hf IDENTIFIED BY relstoragetest;
+ GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE TO relstoragetest_hf;
+ GRANT EXECUTE ON relstorage_util TO relstoragetest_hf;
+ CREATE USER relstoragetest2_hf IDENTIFIED BY relstoragetest;
+ GRANT CONNECT, RESOURCE, CREATE TABLE, CREATE SEQUENCE TO relstoragetest2_hf;
+ GRANT EXECUTE ON relstorage_util TO relstoragetest2_hf;
+
+When running the tests, you can use the environment variable
+ORACLE_TEST_DSN to override the data source name, which defaults to
+"XE" (for Oracle 10g XE).
+
More information about the checkins
mailing list