Re: Lock table

On Wednesday 28 May 2003 4:14 pm, Carmen Wai wrote:
> Hello:
>
> I am using Postgresql 7.2.1, writing c++ program using unixODBC on linux
> platform to access data in the postgresql DB.
>
> My program will start multiple-threads, and each thread will make a
> connection to the DB and insert a record to a table. In order to avoid the
> insertion of duplicate record to the table at the same time, each thread
> will begin a transaction, then lock the table, after insertion of record,
> it will either commit/rollback to unlock the table.

Assuming the threads have their own connections, and your libraries are
thread-safe, all should be fine. If there is a unique constraint on the
relevant columns in your table there's no need to do anything else.
PostgreSQL will enforce constraints and guarantee only one gets inserted -
others will receive an error. You can test this by running two copies of psql
and delaying commits:

psql1> CREATE TABLE foo (a int4 NOT NULL, PRIMARY KEY (a));
psql1> BEGIN;
psql1> INSERT INTO foo VALUES (1);
psql2> BEGIN;
psql2> INSERT INTO foo VALUES (1);
psql1> COMMIT; -- This should work
psql2> COMMIT; -- This should fail, duplicate value for "a"

> When I start my program, I find that there will is a dead lock in the
> locking of table by my threads and whole table will be locked forever until
> I kill my program. Is this the problem on postgresql DB or the unixODBC
> driver?

Sounds like a subtle bug in your thread code somewhere - you can't get
deadlock if you're only locking one table.

> What can I do to control concurrency?

See the chapter "concurrency control" in the User's Guide.

--
Richard Huxton