All Questions
Tagged with transaction-isolation sql
56 questions
2
votes
1
answer
84
views
PostgreSQL doesn't allow two non-conflicting serializable transactions to pass
I'm using PostgreSQL 14. Create the following table:
create table users
(
id serial primary key,
first_name varchar(255) not null,
last_name varchar(255) not null
);
insert into ...
- 4,423
0
votes
1
answer
172
views
How can I verify that my query is causing no locks?
Suppose that I'm having a bad day and my confidence just isn't with me. Suppose also that I've written a query with set transaction isolation level read uncommitted. After it runs for a few minutes, I ...
- 1,642
1
vote
1
answer
1k
views
Isolation -Phantom read in REPEATABLE READ
Does this PARTICULAR case fall into the non-repeatable read category or as a phantom read?
I don't think this QUESTION is DUPLICATED because I have not seen this particular case anywhere.
begin; ...
user19551894
1
vote
0
answers
143
views
Does Using a Linked Server Affect Isolation Levels
I have a query running on one server (let's call it Server B). It looks something like the below - using a linked server to query data from another server ('Server A')
-- in SERVER B
CREATE PROC [foo]
...
- 1,591
0
votes
2
answers
900
views
How to create transaction with isolation level in SQL
How to create a transaction with isolation level in SQL.
I'v tried something like this, but obviously it does not work:
INSERT INTO test(col1) VALUES ('test')
SET TRANSACTION ISOLATION LEVEL read ...
- 683
2
votes
3
answers
3k
views
Prevent lost updates with high transaction isolation levels: Is this a common misconception?
I noticed that my applications often write values to a database that depend on a former read operation. A common example is a bank account where a user could deposit money:
void deposit(amount) {
...
- 3,269
1
vote
2
answers
95
views
SQL Server filter rows which are being selected in other transactions
i have a couple of jobs Update from select queries e.g
UPDATE TABLE_X
SET "stopFlag" = 1
OUTPUT
INSERTED."RowID" AS "rowID"
WHERE "RowID" IN (
SELECT ...
- 25
1
vote
0
answers
623
views
Why would one want a lower isolation level than serializable?
I'm learning about transaction isolation and I'm wondering why one would want a lower isolation level than serializable. What challenges would occur if two different people with isolation level ...
- 117
7
votes
1
answer
5k
views
How does PostgreSQL implement the REPEATABLE_READ isolation level?
The REPEATABLE_READ transaction isolation level of PostgreSQL 12 prevents dirty reads, non-repeatable reads, and phantom reads. In contrast to the READ_COMMITTED isolation level, the REPEATABLE_READ ...
- 137k
0
votes
2
answers
1k
views
How to enforce a phantom read in PostgreSQL?
I'm currently writing an article about different transaction isolation levels and want to show the dirty read / non-repeatable read / phantom read.
Dirty reads are impossible to show as PostgreSQL ...
- 137k
3
votes
1
answer
3k
views
Race conditions between INSERT ON CONFLICT DO NOTHING and SELECT
Does a SELECT query following an INSERT … ON CONFLICT DO NOTHING statement always find a row, given the default transaction isolation (read committed)?
I want to INSERT-or-SELECT a row in one table, ...
- 667k
4
votes
3
answers
12k
views
How to change transaction isolation level globally?
How can I change the default transaction isolation level for the database?
The postgres docs show how to change it per transaction and per session - but not how to alter the default for the database ...
- 12.9k
0
votes
1
answer
1k
views
Unexpected behaviour of the Serializable isolation level
Test setup
I have a SQL Server 2014 and a simple table MyTable that contains columns Code (int) and Data (nvarchar(50)), no indexes created for this table.
I have 4 records in the table in the ...
- 83
1
vote
1
answer
1k
views
Multiple read queries in a single transaction
This answer claims that a transaction is useful for multiple read statements as well. Is the following test case faulty or does it require related tables to return consistent results?
In console #1 ...
- 1,158
4
votes
2
answers
2k
views
Consistency for simultaneous UPDATES in Oracle, when the WHERE clause depends on the old value
I've been reading about Oracle data consistency guarantees, and supported transaction isolation levels, (e.g. here: https://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT121) and I feel like I'm ...
- 2,122