For questions about issues arising when multiple users or processes concurrently access or modify shared information in a database system.
Questions tagged [concurrency]
292 questions
116
votes
1 answer
Postgres UPDATE ... LIMIT 1
I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't care which standby is used in particular.
I want a…
vastlysuperiorman
- 1,315
- 2
- 9
- 8
38
votes
5 answers
Locking issue with concurrent DELETE / INSERT in PostgreSQL
This is pretty simple, but I'm baffled by what PG does (v9.0).
We start with a simple table:
CREATE TABLE test (id INT PRIMARY KEY);
and a few rows:
INSERT INTO TEST VALUES (1);
INSERT INTO TEST VALUES (2);
Using my favorite JDBC query tool…
DaveyBob
- 521
- 2
- 5
- 4
32
votes
4 answers
How do you test for race conditions in a database?
I try to write database code to make sure that it's not subject to race conditions, to make sure that I've locked the correct rows or tables. But I often wonder: Is my code correct? Is it possible to force any existing race conditions to manifest? I…
xenoterracide
- 2,732
- 4
- 27
- 32
27
votes
6 answers
Can I rely on reading SQL Server Identity values in order?
TL;DR: The question below boils down to: When inserting a row, is there a window of opportunity between the generation of a new Identity value and the locking of the corresponding row key in the clustered index, where an external observer could see…
Fabian Schmied
- 525
- 1
- 5
- 10
26
votes
3 answers
Managing concurrency when using SELECT-UPDATE pattern
Let's say you have the following code (please ignore that it's awful):
BEGIN TRAN;
DECLARE @id int
SELECT @id = id + 1 FROM TableA;
UPDATE TableA SET id = @id; --TableA must have only one row, apparently!
COMMIT TRAN;
-- @id is returned to the…
ErikE
- 4,045
- 4
- 25
- 39
23
votes
2 answers
Can I create index on a table in a MySQL database while in use
And if I can, any reason (performance/stability) why I shouldn't?
Nifle
- 1,432
- 7
- 17
- 30
17
votes
3 answers
Concurrent transactions result in race condition with unique constraint on insert
I have a web service (http api) which allows a user to restfully create a resource. After authentication and validation I pass off the data to a Postgres function and allow it to check authorisation and create the records in the database.
I found a…
Elliot Blackburn
- 275
- 1
- 2
- 7
17
votes
2 answers
Locking in Postgres for UPDATE / INSERT combination
I have two tables. One is a log table; another contains, essentially, coupon codes that can only be used once.
The user needs to be able to redeem a coupon, which will insert a row into the log table and mark the coupon as used (by updating the used…
Rob Miller
- 173
- 1
- 1
- 6
16
votes
1 answer
Read a partially updated row?
Let say I have two queries, running in two separate sessions in SSMS:
First session:
UPDATE Person
SET Name='Jonny', Surname='Cage'
WHERE Id=42
Second session:
SELECT Name, Surname
FROM Person WITH(NOLOCK)
WHERE Id > 30
Is it possible that the…
Tesh
- 163
- 5
13
votes
1 answer
PostgreSQL - If I run multiple queries concurrently, under what circumstances would I see a speedup? Under what circumstances would I see a slowdown?
I approach you all humbly as one who is NOT a DBA, and I'm sure that my question is fraught with conceptual shortcomings and "it depends on" land mines. I'm also pretty sure that all of you who choose to answer are going to want a lot more in the…
Aaron Johnson
- 233
- 1
- 2
- 5
13
votes
1 answer
Putting a Select statement in a transaction
What is the difference between these 2 queries:
START TRANSACTION;
SELECT * FROM orders WHERE id=1;
UPDATE orders SET username='John' WHERE id=1;
COMMIT;
And without transaction:
SELECT * FROM orders WHERE id=1;
UPDATE orders SET username='John'…
Songo
- 283
- 1
- 2
- 9
13
votes
3 answers
Inconsistency in repeatable read
http://www.postgresql.org/docs/9.2/static/transaction-iso.html
The Repeatable Read mode provides a rigorous guarantee that each transaction sees a completely stable view of the database. However, this view will not necessarily always be consistent…
alice
- 447
- 1
- 4
- 9
13
votes
3 answers
Insert if not exists, concurrently
I am having concurrency issues with my inserts in a stored procedure. The relevant part of the procedure is this:
select @_id = Id from table1 where othervalue = @_othervalue
IF( @_id IS NULL)
BEGIN
insert into table1 (othervalue) values…
Chris
- 313
- 2
- 6
12
votes
6 answers
Best situation to use READ UNCOMMITTED isolation level
As we all know, READ UNCOMMITTED is the lowest isolation level in which things like dirty reads and phantom reads may accrue. When is the best time to use this isolation level and for what reasons might it be used?
Actually I read the answers…
user123215
12
votes
2 answers
Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL
If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished?
I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until the new one has finished, so I need to know when…
Amandasaurus
- 667
- 1
- 7
- 14