Concurrency issues

Поиск
Список
Период
Сортировка
От Clarence Gardner
Тема Concurrency issues
Дата
Msg-id Pine.LNX.4.44.0301051847340.1399-100000@liberty.sba2.netlojix.net
обсуждение исходный текст
Ответы Re: Concurrency issues  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general

I've recently moved a system from Oracle to Postgre, and I'm having
locking problems.

Here's a simple scenario, with a one-row table:
  Process 1 does the following:
      netbill=> begin;
      BEGIN
      netbill=> update t1 set f1=2;
      UPDATE 1
      netbill=>

and at this point, the locks are:
    netbill=> select * from pg_locks order by pid;
     relation | database | transaction |  pid  |       mode       | granted
    ----------+----------+-------------+-------+------------------+---------
              |          |      422665 | 28283 | ExclusiveLock    | t
     17715590 |    17203 |             | 28283 | AccessShareLock  | t
     17715590 |    17203 |             | 28283 | RowExclusiveLock | t

Now Process 2 does the same thing:
    netbill=> begin;
    BEGIN
    netbill=> update t1 set f1=3;
    (blocks)

And now, the locks:
netbill=> select * from pg_locks order by pid;
 relation | database | transaction |  pid  |       mode       | granted
----------+----------+-------------+-------+------------------+---------
          |          |      422665 | 28283 | ExclusiveLock    | t
 17715590 |    17203 |             | 28283 | AccessShareLock  | t
 17715590 |    17203 |             | 28283 | RowExclusiveLock | t
          |          |      422665 | 28284 | ShareLock        | f
 17715590 |    17203 |             | 28284 | AccessShareLock  | t
 17715590 |    17203 |             | 28284 | RowExclusiveLock | t
          |          |      422670 | 28284 | ExclusiveLock    | t

Some questions:
  1) What exactly is the transaction id? I see that not only does Process 2
     have two different xid's (in addition to the nulls), but that one of
     them is shared with process 1. This seems odd to the casual observer.
  2) What resource is the blocking lock attempting to lock? It has no
     relation or database value.
  3) According to http://www.postgresql.org/idocs/index.php?locking-tables.html,
     all of those locking modes are table-level locks. Am I silly in expecting
     that the blocked Process 2 should be waiting on a row-level lock?
  4) What is it that both processes have an ExclusiveLock on?

5) Have I missed some documentation somewhere?

Thanks for any help.

clarence@netlojix.com


В списке pgsql-general по дате отправления:

Предыдущее
От: Jean-Christian Imbeault
Дата:
Сообщение: serverlog problems
Следующее
От: Albert Cervera Areny
Дата:
Сообщение: Fwd: Stock update like application