Transaction isolation relates to the visibility and impact of changes made in one transaction on concurrent transactions.
Questions tagged [transaction-isolation]
256 questions
0
votes
2 answers
Hold exclusive lock for select query
SQL Server: how to hold exclusive lock for select query? For mysql,
select * from Employee e
where e.id=123
for update
Other concurrent transaction can not read or write the selected row.
How to achieve the same for SQL server?
SELECT *
FROM …
eastwater
- 4,624
- 9
- 49
- 118
0
votes
1 answer
near "SET": syntax error , while trying to set isolation level
I want to create a transaction in go and while doing that I get error : near "SET": syntax error. The code:
db.Exec("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;")
if err := db.Exec("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED").Error; err…
chameleon123
- 53
- 7
0
votes
1 answer
Prevent two threads from selecting same row ibm db2
I have a situation where I have multiple (potentially hundreds) threads repeating the same task (using a java scheduled executor, if you are curious). This task entails selecting rows of changes (from a table called change) that have not yet been…
Connor Butch
- 648
- 1
- 10
- 28
0
votes
0 answers
Innodb executing "select for update" and "select (consistent read) in repeatable read isolation" parallelly in two different sessions/transactions
I have two parallel sessions/transactions, where one reads with "for update" and other with "consistent read(default)" . I want to know whether select query in session-2 blocks until session-1 is committed or executes?
session-1
START TRANSACTION…
Pushparaj
- 1,039
- 1
- 6
- 26
0
votes
1 answer
How do I know which transactions run first
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
INSERT INTO Students VALUES(’Jason’,50);
UPDATE Students SET mark = mark + 10;
COMMIT
SET TRANSACTION ISOLATION READ COMMITED
INSERT INTO Students VALUES (’Kylie’,70);
SELECT SUM(mark) FROM…
Dabig
- 1
0
votes
2 answers
Is Oracle DB truly isolated during execution of COMMIT?
Consider these two transactions:
INSERT INTO foo VALUES (1, 2, 'bar');
INSERT INTO foo VALUES (1, 4, 'xyz');
COMMIT;
and
SELECT * FROM foo;
Is there any point in time when the SELECT would see only one row inserted from the first transaction?
So…
Jakub Zaverka
- 8,816
- 3
- 32
- 48
0
votes
0 answers
Which transaction level is best suited to read records from an application event log table?
I'm implementing a background process for moving event log records from SQL database to mongoDB.
Event log / audit trail entries are known to change only once at the end of the event. The process is like this:
1) an event log entry gets created to…
JustAMartin
- 13,165
- 18
- 99
- 183
0
votes
1 answer
Repeatable read regarding select * with order by and limit
At the default isolation level of mysql (Repeatable Read), if I issue a select like select * from table_a where column_a = 'a' order by id limit 100, and after a while, I issued another statement within the same transaction like select * from…
Danny
- 5
- 4
0
votes
1 answer
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 execute the following:
set transaction isolation…
Jani
- 1,088
- 1
- 10
- 18
0
votes
0 answers
Prevent INSERT INTO ... SELECT statement from creating its own fresh snapshot
Is it possible for MySQL/MariaDb InnoDb engine to do INSERT INTO ... SELECT statement and use snapshot created by current REPEATABLE READ transaction?
Example (all statements run within one session except one mentioned below):
SET TRANSACTION…
RaDim
- 630
- 6
- 6
0
votes
0 answers
How to use Transaction type Snapshot in PostgreSQL using java
I have a requirement to execute DML commands in a microservice on the same database (Postgres) and commit in two phases.
I found transaction isolation snapshot as one of the solutions. I found in the documentation to create snapshot id first and use…
Shanmukha
- 11
- 1
0
votes
2 answers
Prevent two transactions from creating the same entity but still allow concurrent creation
I have a system that interfaces with a third party system to create and store car data. The user selects a ThirdPartyCar and uses it to create a Car in my system.
A service method saves the car. But it should only save if someone else has not…
James
- 2,876
- 18
- 72
- 116
0
votes
0 answers
Postgres: transaction isolation using a fetch size
An application is connected to Postgres using jdbi (dropwizard). We can set it up to do SELECT with a given fetch size.
Ex: with a fetch size of 10 and
SELECT * FROM mytable LIMIT 100
It will send a single query but return the result in 10 times.…
Rolintocour
- 2,934
- 4
- 32
- 63
0
votes
1 answer
SQL Server: atomic update in read committed, can detect concurrent write?
If I have concurrent transactions both using read committed isolation level and body of the transaction look like this:
DECLARE @value..
SELECT @value = balance
FROM MyTable
WHERE Account = 1
UPDATE MyTable
SET balance = @value+@pAmount
WHERE…
user21479
- 1,179
- 2
- 13
- 21
0
votes
1 answer
can two InnoDB UPDATE statements against a PK index deadlock if we rely on index scan ordering in the statement?
If we are given a table:
MariaDB [test]> create table foo (
-> id integer primary key,
-> version_id integer);
Query OK, 0 rows affected (0.05 sec)
and two rows with primary key 1 and 2:
MariaDB [test]> insert into foo (id, version_id)…
zzzeek
- 72,307
- 23
- 193
- 185