PG从小工到专家学习笔记-事务、并发、锁.pdf

蔡璐

云和恩墨交付战队

359

1页

9次

2021-12-06

5墨值下载





ACCESSSHARE

ROWSHARE

 ACCESSEXCLUSlVE 

SELECT

 EXCLUSIVE ACCESSEXCLUSIVE

SELECTFORUPDATE SELECTFORSHARE



 SHARE SHAREROWEXCLUSIVE EXCLUSIVE ACCESSEXCLUSIVE  UPDATE DELETE INSERT

 

ROWEXCLUSIVE



SHAREUPDATEEXCLUSIVE

 SHAREUPDATEEXCLUSIVE SHARE SHAREROWEXCLUSIVE EXCLUSIVE ACCESSEXCLUSIVE 

 VARCUM .  VACUM( FULL ) ANALYZE CREATElNDEXCONCURR-

ENTLY



SHARE

 ROWEXCLUSIVE SHAREUPDATEEXCLUSIVE SHAREROWEXCLUSIVE EXCLUSIVE ACCESSEXCLUSIVE

    CREATEINDEX( CONCURRENTLY )



SHAREROWEXCLUSIVE

 ROWEXCLUSIVE SHAREUPDATEEXCLUSIVE SHARE SHAREROWEXCLUSIVE EXCLUSIVE ACCESSEXCLUSIVE



 PostgreSQL



EXCLUSIVE

 ROWSHARE ROWEXCLUSIVE SHAREUPDATEEXCLUSIVE SHARE SHAREROWEXCLUSIVE EXCLUSIVE 

ACCESSEXCLUSIVE  ACCESSSHARE 

 

 PostgreSQL 



ACCESSEXCLUSIVE

 ( ACESSSHARE ROWSHARE ROWEXCLUSIVE,SHAREUPDATEEXCLUSIVE SHARE SHAREROW

EXCLUSIVE EXCLUSIVE ACCESSEXCLUSIVE  )



ALTERTABLE DROPTABLE TRUNCATE REINDEX CLUSTER

VACUUMFULL  LOCKTABLE



mysql



 MVCC



 PostgreSQL

 



LOCKTABLE

 LOCK[TABLE][ONLY]name[,...][

INlockmodeMODE)[NOWAIT]





 SELECT.....FOR(UPDATEISHAREI[

OF'tablename[I•••JJ[NOWAITl[...])





pg_locks pg_locks



















DDL







AUTOCOMMIT

setAUTOCOMMIToff

 \echo:AUTOCOMMIT

 begin

SAVEPOINT

savepointmysavepointOl;

rollbacktoSAVEPOINTmy_savepointOl;



READUNCOMMITTED:

READCOMMITTED:





REPEATABLEREAD:

SERIALIZABLE:





PREPARETRANSACTION







 

 commit/rollback



ACID





ACCESSSHARE

ROWSHARE

 ACCESSEXCLUSlVE 

SELECT

 EXCLUSIVE ACCESSEXCLUSIVE

SELECTFORUPDATE SELECTFORSHARE



 SHARE SHAREROWEXCLUSIVE EXCLUSIVE ACCESSEXCLUSIVE  UPDATE DELETE INSERT

 

ROWEXCLUSIVE



SHAREUPDATEEXCLUSIVE

 SHAREUPDATEEXCLUSIVE SHARE SHAREROWEXCLUSIVE EXCLUSIVE ACCESSEXCLUSIVE 

 VARCUM .  VACUM( FULL ) ANALYZE CREATElNDEXCONCURR-

ENTLY



SHARE

 ROWEXCLUSIVE SHAREUPDATEEXCLUSIVE SHAREROWEXCLUSIVE EXCLUSIVE ACCESSEXCLUSIVE

    CREATEINDEX( CONCURRENTLY )



SHAREROWEXCLUSIVE

 ROWEXCLUSIVE SHAREUPDATEEXCLUSIVE SHARE SHAREROWEXCLUSIVE EXCLUSIVE ACCESSEXCLUSIVE



 PostgreSQL



EXCLUSIVE

 ROWSHARE ROWEXCLUSIVE SHAREUPDATEEXCLUSIVE SHARE SHAREROWEXCLUSIVE EXCLUSIVE 

ACCESSEXCLUSIVE  ACCESSSHARE 

 

 PostgreSQL 



ACCESSEXCLUSIVE

 ( ACESSSHARE ROWSHARE ROWEXCLUSIVE,SHAREUPDATEEXCLUSIVE SHARE SHAREROW

EXCLUSIVE EXCLUSIVE ACCESSEXCLUSIVE  )



ALTERTABLE DROPTABLE TRUNCATE REINDEX CLUSTER

VACUUMFULL  LOCKTABLE



mysql



 MVCC



 PostgreSQL

 



LOCKTABLE

 LOCK[TABLE][ONLY]name[,...][

INlockmodeMODE)[NOWAIT]





 SELECT.....FOR(UPDATEISHAREI[

OF'tablename[I•••JJ[NOWAITl[...])





pg_locks pg_locks



















DDL







AUTOCOMMIT

setAUTOCOMMIToff

 \echo:AUTOCOMMIT

 begin

SAVEPOINT

savepointmysavepointOl;

rollbacktoSAVEPOINTmy_savepointOl;



READUNCOMMITTED:

READCOMMITTED:





REPEATABLEREAD:

SERIALIZABLE:





PREPARETRANSACTION







 

 commit/rollback



ACID

of 1

5墨值下载

postgresql

关注

评论