begin;
update table foo set ...;
commit;
If two sessions execute the commands above, even if they are updating different rows,
one of them is going to fail (assuming the SQL runs long enough so that both sessions
update the table at the same time).
If you do not put the update statement in a begin/commit block,
you can have multiple sessions updating at the same time but they will get serialized.
That is in general not a good design pattern though and I cannot recommened it
as a way to update anything that is important (fine for log tables, bad for dimension and fact tables).
http://jackwxh.blog.51cto.com/2850597/855990
Same for Store Procedure, one real case:
2015-09-15 10:55:12.134655 EDT [4706] DEBUG: QUERY: exec STP_1()
2015-09-15 10:55:12.142716 EDT [4706] DEBUG: STORED PROCEDURE EXEC: DELETE FROM ...
2015-09-15 10:55:12.266968 EDT [14106] DEBUG: logDir is : /tmp
2015-09-15 10:55:12.266990 EDT [14106] DEBUG: sending nzlog '/nz/kit.7.0.2.P4/log/nzloadTmpLogs/BULKETL_14106_0-14106.nzlog' to FE as BULKETL_14106_0.EMBS.nzlog
2015-09-15 10:55:12.267115 EDT [14106] DEBUG: done sending '/nz/kit.7.0.2.P4/log/nzloadTmpLogs/BULKETL_14106_0-14106.nzlog' to FE
2015-09-15 10:55:12.267131 EDT [14106] DEBUG: sending nzbad '/nz/kit.7.0.2.P4/log/nzloadTmpLogs/BULKETL_14106_0-14106.nzbad' to FE as BULKETL_14106_0.EMBS.nzbad
2015-09-15 10:55:12.267147 EDT [14106] DEBUG: nzbad file '/nz/kit.7.0.2.P4/log/nzloadTmpLogs/BULKETL_14106_0-14106.nzbad' not found, no errors found during load
2015-09-15 10:55:12.271244 EDT [14106] DEBUG: 55 rows inserted
2015-09-15 10:55:12.284825 EDT [14106] DEBUG: QUERY: drop table bulkETL_14106_0
2015-09-15 10:55:12.299180 EDT [14106] DEBUG: QUERY: exec STP_1()
2015-09-15 10:55:12.304960 EDT [14106] DEBUG: STORED PROCEDURE EXEC: DELETE FROM ...
2015-09-15 10:55:12.328187 EDT [14106] ERROR: TABLENAME : Could not serialize - transaction aborted
2015-09-15 10:55:12.328256 EDT [14106] NOTICE: Error occurred while executing PL/pgSQL function STP_1
2015-09-15 10:55:12.328290 EDT [14106] NOTICE: line 1 at SQL statement
2015-09-15 10:55:12.350886 EDT [4706] DEBUG: 347 rows deleted
2015-09-15 10:55:12.352285 EDT [4706] DEBUG: STORED PROCEDURE EXEC: ...
2015-09-15 10:55:12.400821 EDT [28125] DEBUG: InvalidateSharedInvalid: cache state reset
2015-09-15 10:55:12.402748 EDT [28125] DEBUG: QUERY: select current_timestamp
2015-09-15 10:55:12.442931 EDT [28125] DEBUG: QUERY: select current_timestamp
2015-09-15 10:55:12.469193 EDT [28125] DEBUG: QUERY: select current_timestamp
2015-09-15 10:55:12.555778 EDT [4706] DEBUG: 1055 rows inserted
2015-09-15 10:55:12.556273 EDT [4706] DEBUG: STORED PROCEDURE EXEC: DELETE FROM ...
2015-09-15 10:55:12.605269 EDT [4706] DEBUG: 1055 rows deleted
阅读(2142) | 评论(0) | 转发(0) |