Lists: | pgsql-hackers |
---|
From: | 黄晓骋 <huangxclife(at)gmail(dot)com> |
---|---|
To: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | questions about concurrency control in Postgresql |
Date: | 2009-12-08 04:05:45 |
Message-ID: | 000901ca77bb$b9e961b0$2dbc2510$@com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Hello,
I think in Postgresql, concurrency control acts like this:
tuple's infomask shows if it is being updated. If it is being updated now,
the latter transaction should reread the tuple and get the newer tuple.
During the progress of getting the newer tuple, it must use transaction
lock, I mean XactLockTableWait(...).
From the above, I think the tuple lock is unnecessary, because it uses
transaction lock.
Besides, tuple lock is unlocked after the tuple is updated but not after the
transaction commits. I mean it's not 2PL.
So, may you tell me why there is tuple lock in Postgresql ? Is the tuple
lock necessary?
Thanks,
--Huang Xiaocheng
--Database & Information System Lab, Nankai University
From: | Daniel Farina <drfarina(at)gmail(dot)com> |
---|---|
To: | 黄晓骋 <huangxclife(at)gmail(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: questions about concurrency control in Postgresql |
Date: | 2009-12-08 06:01:04 |
Message-ID: | 7b97c5a40912072201h6575201eid6597fc42052dd18@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
2009/12/7 黄晓骋 <huangxclife(at)gmail(dot)com>:
> Hello,
>
> I think in Postgresql, concurrency control acts like this:
>
> tuple's infomask shows if it is being updated. If it is being updated now,
> the latter transaction should reread the tuple and get the newer tuple.
> During the progress of getting the newer tuple, it must use transaction
> lock, I mean XactLockTableWait(...).
That is a table lock...depending on the lock, other backends may be
allowed to update tuples in the relation still. Fine-grained tuple
locks are used to prevent unnecessary contention for a table-wide
lock.
See the documentation at the manual page:
http://www.postgresql.org/docs/8.4/static/explicit-locking.html
It gives a thorough treatment of table and row locking levels and
conflicts, as well as what gets what locks.
fdr
From: | Greg Stark <gsstark(at)mit(dot)edu> |
---|---|
To: | 黄晓骋 <huangxclife(at)gmail(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: questions about concurrency control in Postgresql |
Date: | 2009-12-08 12:16:06 |
Message-ID: | 407d949e0912080416vd8377c8pb6877d64254138fb@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
2009/12/8 黄晓骋 <huangxclife(at)gmail(dot)com>:
> From the above, I think the tuple lock is unnecessary, because it uses
> transaction lock.
>
> Besides, tuple lock is unlocked after the tuple is updated but not after the
> transaction commits. I mean it's not 2PL.
It's a two step process. An update marks the tuple locked. Another
transaction which comes along and wants to lock the tuple waits on the
transaction marked on the tuple. When the first transaction commits or
aborts then the second transaction can proceed and lock the tuple
itself. The reason we need both locks is because the first transaction
cannot go around the whole database finding every tuple it ever locked
to unlock it, firstly that could be a very large list and secondly
there would be no way to do that atomically.
Tuple locks and all user-visible locks are indeed held until the end
of the transaction.
--
greg
From: | 黄晓骋 <huangxclife(at)gmail(dot)com> |
---|---|
To: | "'Greg Stark'" <gsstark(at)mit(dot)edu> |
Cc: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | 答复: questions about concurrency control in Postgresql |
Date: | 2009-12-09 07:33:44 |
Message-ID: | 002101ca78a1$f2e433f0$d8ac9bd0$@com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
>It's a two step process. An update marks the tuple locked. Another
>transaction which comes along and wants to lock the tuple waits on the
>transaction marked on the tuple. When the first transaction commits or
>aborts then the second transaction can proceed and lock the tuple
>itself.
I agree with it.
>The reason we need both locks is because the first transaction
>cannot go around the whole database finding every tuple it ever locked
>to unlock it, firstly that could be a very large list and secondly
>there would be no way to do that atomically.
You mean that 2PL is hard to realize actually, I agree too.
But it doesn't mean tuple lock is necessary.
>Tuple locks and all user-visible locks are indeed held until the end
>of the transaction.
I don't agree with it, for I see unlocktuple(...) in heap_update(...).
--Huang Xiaocheng
--Database & Information System Lab, Nankai University
__________ Information from ESET NOD32 Antivirus, version of virus signature database 4671 (20091208) __________
The message was checked by ESET NOD32 Antivirus.
From: | 黄晓骋 <huangxclife(at)gmail(dot)com> |
---|---|
To: | "'Greg Stark'" <gsstark(at)mit(dot)edu> |
Cc: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | 答复: questions about concurrency control in Postgresql |
Date: | 2009-12-10 06:47:14 |
Message-ID: | 000e01ca7964$9f526c70$ddf74550$@com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
I think I know why we need tuple lock.
Though we have tuple's infomask shows whether the tuple is being updated, before we set the tuple's infomask, there may be two transaction coming and updating the tuple. They both think the tuple is ok to be updated, and then it's wrong.
In PostgreSQL, we can use buffer lock to solve the problem , but its granularity is not proper. So we must use tuple lock to solve the problem.
Thank you, Greg. You prompt me to think clearly about it.
Happy communicating with you, and thanks again.
--Huang Xiaocheng
--Database & Information System Lab, Nankai University
-----邮件原件-----
发件人: gsstark(at)gmail(dot)com [mailto:gsstark(at)gmail(dot)com] 代表 Greg Stark
发送时间: 2009年12月8日 20:16
收件人: 黄晓骋
抄送: pgsql-hackers(at)postgresql(dot)org
主题: Re: questions about concurrency control in Postgresql
2009/12/8 黄晓骋 <huangxclife(at)gmail(dot)com>:
> From the above, I think the tuple lock is unnecessary, because it uses
> transaction lock.
>
> Besides, tuple lock is unlocked after the tuple is updated but not after the
> transaction commits. I mean it's not 2PL.
It's a two step process. An update marks the tuple locked. Another
transaction which comes along and wants to lock the tuple waits on the
transaction marked on the tuple. When the first transaction commits or
aborts then the second transaction can proceed and lock the tuple
itself. The reason we need both locks is because the first transaction
cannot go around the whole database finding every tuple it ever locked
to unlock it, firstly that could be a very large list and secondly
there would be no way to do that atomically.
Tuple locks and all user-visible locks are indeed held until the end
of the transaction.
--
greg
__________ Information from ESET NOD32 Antivirus, version of virus signature database 4671 (20091208) __________
The message was checked by ESET NOD32 Antivirus.
__________ Information from ESET NOD32 Antivirus, version of virus signature database 4674 (20091209) __________
The message was checked by ESET NOD32 Antivirus.
From: | 黄晓骋 <huangxclife(at)gmail(dot)com> |
---|---|
To: | "'Alvaro Herrera'" <alvherre(at)commandprompt(dot)com> |
Cc: | "'Greg Stark'" <gsstark(at)mit(dot)edu>, <pgsql-hackers(at)postgresql(dot)org> |
Subject: | 答复: [HACKERS] 答复: questions about concurrency control in Postgresql |
Date: | 2009-12-15 01:17:32 |
Message-ID: | 000001ca7d24$646a4060$2d3ec120$@com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
You are right. I never consider the SELECT FOR UPDATE/SHARE type queries, so I got the wrong conclusion.
I have seen the content in the comment of heap_lock_tuple().
Thank you,
Best Regards,
--Huang Xiaocheng
--Database & Information System Lab, Nankai University
-----邮件原件-----
发件人: Alvaro Herrera [mailto:alvherre(at)commandprompt(dot)com]
发送时间: 2009年12月10日 22:54
收件人: 黄晓骋
抄送: 'Greg Stark'; pgsql-hackers(at)postgresql(dot)org
主题: Re: [HACKERS] 答复: questions about concurrency control in Postgresql
黄晓骋 escribió:
> I think I know why we need tuple lock.
> Though we have tuple's infomask shows whether the tuple is being updated, before we set the tuple's infomask, there may be two transaction coming and updating the tuple. They both think the tuple is ok to be updated, and then it's wrong.
> In PostgreSQL, we can use buffer lock to solve the problem , but its granularity is not proper. So we must use tuple lock to solve the problem.
> Thank you, Greg. You prompt me to think clearly about it.
Actually it's the buffer lock that's used to protect most of infomask.
Tuple locks are only used while XMAX and some infomask bits are set for
SELECT FOR UPDATE/SHARE type queries. That can take a while because it
may need I/O in pg_multixact, so the buffer lock is not appropriate to
hold for so long.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
__________ Information from ESET NOD32 Antivirus, version of virus signature database 4677 (20091210) __________
The message was checked by ESET NOD32 Antivirus.
__________ Information from ESET NOD32 Antivirus, version of virus signature database 4687 (20091214) __________
The message was checked by ESET NOD32 Antivirus.