PostgreSQL 8.0.0 ��ĵ��PostgreSQL �й� ��
Prev	Fast Backward	Chapter 12. ��	Fast Forward	Next

12.3. ��ȷ��

PostgreSQL �ṩ�˸��ָ��ģʽ��ڿ��ƶԱ��е��ݵĲ��ʡ� ��Щģʽ�� MVCC �޷��Ϊ��ʱ��Ӧ�ÿ��Ƶ�� ͬ�� PostgreSQL ��Զ�ʩ��ǡ��Ա�֤��õı��ִ�е�ʱ�򲻻��һ�ֲ��ݵķ�ʽ��ɾ��޸ġ� ��磬�ڴ��ʱ��ALTER TABLE �ǲ��ͬһ��ִ�еġ��

Ҫ��һ�е�ǰ��ݿ��ڳ��е��ǿ��ʹ��ϵͳ��ͼ pg_locks ��Section 41.33��йؼ��ϵͳ��״̬�ĸ��Ϣ��ο� Chapter 23��

12.3.1. ��

��б��ʾ�˿��õ��ģʽ��Ǳ� PostgreSQL �Զ�ʹ�õĻ�� Ҳ�� LOCK ��ȷ��ȡ��Щ�� ע��Щ��ģʽ��Ǳ��ʹ��ǵ��ְ�� "row"��Щ��ģʽ��ʷ��ɵġ� ��ĳ�ֽǶȶ��ԣ��Щ��ַ�Ӧ��ÿ��ģʽ�ĵ��÷� — ��ⶼ��һ��ġ� ��ģʽ֮��Ų�ͬ�ĳ�ͻ��ϡ� ��ͬһʱ�̲��ͬһ��ϳ��໥��ͻ�� һ��ͻ��磬��һ�� ACCESS EXCLUSIVE Ȼ��Ժ��ʱ�� ACCESS SHARE�� ǳ�ͻ��ģʽ��񲢷��س��С� ��ر�ע��Щ��ģʽ��Գ�ͻ�ģ��磬��ʱ�� ACCESS EXCLUSIVE ģʽ�Ͳ��ܹ��ӵ�У� ��ض��Գ�ͻ�ģ��磬ACCESS SHARE ��Ա��У�� һ��ĳ��ô��ģʽ��

��ģʽ

ACCESS SHARE

ֻ�� ACCESS EXCLUSIVE ��ͻ��

SELECT �� ANALYZE ��ڱ��õı��һ�� ͨ��κ�ֻ��ȡ��޸����ģʽ��

ROW SHARE

��EXCLUSIVE��ACCESS EXCLUSIVEģʽ��ͻ��

SELECT FOR UPDATE ��Ŀ��Ҫһ��ģʽ��б��õ�û�� FOR UPDATE �ı��ϵ� ACCESS SHARE ��

ROW EXCLUSIVE

�� SHARE��SHARE ROW EXCLUSIVE�� EXCLUSIVE �� ACCESS EXCLUSIVE ģʽ��ͻ��

�� UPDATE��DELETE�� INSERT �Զ��ģʽ�� õı��ϵ� ACCESS SHARE �� ͨ��κ��޸ı��ݵĲ�ѯ��

SHARE UPDATE EXCLUSIVE

�� SHARE UPDATE EXCLUSIVE��SHARE�� SHARE ROW EXCLUSIVE��EXCLUSIVE�� ACCESS EXCLUSIVE ģʽ��ͻ�� ģʽ��һ��ģʽ�ı�� VACUUM��

VACUUM�� FULL ѡ���

SHARE

�� ROW EXCLUSIVE��SHARE UPDATE EXCLUSIVE�� SHARE ROW EXCLUSIVE��EXCLUSIVE �� ACCESS EXCLUSIVE ģʽ��ͻ�� ģʽ��Ĳ��޸ġ�

CREATE INDEX ��Ҫ��ģʽ��

SHARE ROW EXCLUSIVE

�� ROW EXCLUSIVE�� SHARE UPDATE EXCLUSIVE��SHARE�� SHARE ROW EXCLUSIVE��EXCLUSIVE�� ACCESS EXCLUSIVE ģʽ��ͻ��

�κ� PostgreSQL ����Զ��ģʽ��

EXCLUSIVE LOCK

�� ROW SHARE��ROW EXCLUSIVE�� SHARE UPDATE EXCLUSIVE�� SHARE��SHARE ROW EXCLUSIVE�� EXCLUSIVE �� ACCESS EXCLUSIVE ģʽ��ͻ�� ģʽֻ�� ACCESS SHARE ��Ҳ��˵�� ֻ�жԱ�Ķ��Ժͳ��ģʽ��ִ�С�

�κ� PostgreSQL ����Զ��ģʽ��

ACCESS EXCLUSIVE

��ģʽ��ͻ�� ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, �� ACCESS EXCLUSIVE). ��ģʽ��֤��ߣ��ǿ��ⷽʽ��ʸñ��Ψһ��

ALTER TABLE�� DROP TABLE��REINDEX��CLUSTER�� VACUUM FULL ��Ҫ�� LOCK TABLE ��û��ȷ��Ҫ��ģʽʱ��Ҳ��ȱʡ��ģʽ��

��ʾ: ֻ�� ACCESS EXCLUSIVE �� SELECT ��û�� FOR UPDATE��䣩��

12.3.2. �м��

��˱��⣬��м�� ض��ϵ��м��б��µ�ʱ��Զ��ģ��߱�ɾ��ʱ��Ϊ��£�� һֱ��ֵ��ύ��߻ع�� м��Ӱ��ݵĲ�ѯ�� ֻ��ͬһ�е�д���� Ҫ�ڲ��޸�ĳ�е�ǰ��ڸ��е��м�� SELECT FOR UPDATE ѡȡ��С��ע��һ��ض��м�� ô��Ϳ��Զ�ζԸ��н��и��¶��õ��ĳ�ͻ��

PostgreSQL ��ڴ��ﱣ��κι��޸��е��Ϣ�� ˶�һ��û��ơ� ��סһ�лᵼ��һ�δ��д��ˣ�� SELECT FOR UPDATE ��޸�ѡ�е��Ա��ǣ� ��˻ᵼ�´��д��

��˱��ĺ��м��⣬ ҳ�漶��Ĺ��/��Ҳ��ڿ��ƶԹ��б�ҳ��Ķ�/д��ʡ� ��Щ��ץȡ��߸��һ�к��ϱ��ͷš� Ӧ�ó��Աͨ��Ҫ��ҳ��ᵽ��ֻ��Ϊ��

12.3.3. ��

��ȷ��ʹ�ÿ��ܻ�����Ŀ��ԣ� ��ָ��໥��жԷ��ڴ��磬 �� 1 �ڱ� A�ϳ��һ�� ͬʱ��ͼ��һ��ڱ� B �ϵ�� 2 �Ѿ��б�B��ȴ��ڱ� A�ϵ�һ��ô��Ͷ��ִ�С� PostgreSQL �Զ��⵽��һ�ͨ��˳�һ��µ��⣬ �Դ��ɡ��ĸ��ᱻ�˳��Ǻ��Ԥ�Ƶģ��Ҳ��Ӧ��Ԥ�ơ��

Ҫע��Ҳ��ܻ��Ϊ�м��ˣ��ʹ��û��ʹ��ȷ��Ҳ��ܷ�� һ��޸�һ��һ��ִ��ˣ�

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;

��ָ��ʺŵ��һ��м��Ȼ�󣬵ڶ��ִ�У�

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;

��һ�� UPDATE ��ɹ��ָ��һ��м��ɹ��˸��С� ��ǵڶ�� UPDATE ��䷢��ͼ��µ��Ѿ��ס�ˣ� ��ȴ��и��ھ��ڵȴ��һ��Ȼ��ټ��ִ�С� ��ڣ��һִ�У�

UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;

��һ��ͼ��ָ��һ��м��ò��Ѿ��ˡ� ��ȴ��ɡ��ˣ��һ��ס�ˣ��һ��ס�ˣ��һ�� PostgreSQL ��˳��һ��

��ֹ��÷��ͨ��Ǳ�֤��ʹ��һ��ݿ��Ӧ�ö��һ�µ�˳��ڶ�� ��ͬ��˳��Щ�У��ô�Ͳ��ᷢ�� ҲҪ��֤��һ��ĵ�һ��Ǹö��Ҫ��ߵ��ģʽ�� ޷��ǰ��ʵ��Щ��⣬��ô��ǿ��ͨ��ֳ��³��˳��ķ��

ֻҪû�м�⵽��һ��ȴ��м��񽫵ȴ��ͻ��ͷŲ�ȷ��ʱ�䡣 ��ζ��һ��Ӧ�ó��д򿪵��ʱ��̫��ɲ��ʲô��飨��ȴ��û��룩��

12.3. ��ȷ����

12.3.1. ����

12.3.2. �м���

12.3.3. ����

12.3. ��ȷ��

12.3.1. ��

12.3.2. �м��

12.3.3. ��