New answers tagged transaction
2
To summarize and close (at least for now) this question:
Original issue stems from the fact, that MySQL does not guarantee valid and consistent comparison of BIGINT against strings (VARCHAR or not) as confirmed in https://bugs.mysql.com/bug.php?id=103992
PHP currently does not support BIGINT. And even if getting such values as strings can be acceptable (at ...
0
connect to the database as a superuser role (eg: postgres)
begin a smart shutdown
repeatedly execute this SQL in your connection.
select pid, case when xact_start is not null then state else
pg_terminate_backend(pid) ::text end from pg_stat_activity;
when that returns only a single row all other users have been disconnected.
2
Write-ahead logging guarantees that log records describing a change (and how to undo it) are written to persistent storage before the changed data is.
Log records do not necessarily wait for a commit before being persisted. Changed data may also be written to persistent storage (e.g. by checkpoint) before transaction commit, but only if the associated log ...
2
I think it depends on whether you want to rely on the linked server configuration option 'Enable Promotion of Distributed Transaction' or whether
you want to be sure that your transaction can run only if it's distributed.
5
One way is to use deferred constraints.
CREATE TABLE tst(x int CONSTRAINT test_me UNIQUE DEFERRABLE INITIALLY DEFERRED);
begin;
insert into tst values(1);
insert into tst values(1); -- Note: no error at this point
commit;
The COMMIT fails with:
ERROR: duplicate key value violates unique constraint "test_me"
DÉTAIL : Key (x)=(1) already ...
1
SET autocommit = ON;
INSERT ...
pause
SELECT ...
During the 'pause' some other thread could DELETE or UPDATE that row. In this case, the SELECT would not find it.
BEGIN;
INSERT ...
pause
SELECT ...
COMMIT;
Now, the INSERT and SELECT are part of the same transaction. No other thread can mess with row in question. The SELECT will find the row. (There is ...
6
What Laurenz said. Be aware that Postgres checks (has to check) across transaction isolation boundaries for unique violations (the exception raised by a duplicate entry into a UNIQUE index). Those checks are "absolute".
Details in the chapter "Index Uniqueness Checks" in the manual.
But use this optimised, equivalent solution, while being ...
5
For that, you simply put a constraint on visit in the shape of a unique index:
CREATE UNIQUE INDEX ON visit (place_id, date_trunc('day', place_dt));
That will guarantee uniqueness, no matter what the isolation level is.
Top 50 recent answers are included
