Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [concurrency]

For questions about issues arising when multiple users or processes concurrently access or modify shared information in a database system.

331 questions
151
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,685
  • 2
  • 11
  • 8
42
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
  • 561
  • 2
  • 5
  • 4
37
votes
5 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,921
  • 5
  • 31
  • 33
30
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
  • 597
  • 1
  • 5
  • 10
29
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,355
  • 4
  • 29
  • 39
27
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
  • 1,007
  • 4
  • 13
  • 15
25
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
  • 365
  • 1
  • 3
  • 8
24
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,472
  • 8
  • 17
  • 31
23
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
  • 333
  • 1
  • 2
  • 5
22
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
  • 323
  • 1
  • 2
  • 6
17
votes
7 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
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…
Sergei Namniasov
  • 163
  • 5
16
votes
2 answers

Is it possible for SQL statements to execute concurrently within a single session in SQL Server?

I have written a stored procedure which makes use of a temporary table. I know that in SQL Server, temporary tables are session-scoped. However, I have not been able to find definitive information on exactly what a session is capable of. In…
Trevor Giddings
  • 275
  • 3
  • 8
16
votes
2 answers

Is the system column "ctid" legitimate for identifying rows to delete?

I have a table with hundreds of millions of rows that I need to delete data from. The existing indexes are the most efficient. I can however use the existing indexes to find the rows to delete by using the ctid values: DELETE FROM calendar_event…
ruelloehr
  • 261
  • 2
  • 3
16
votes
1 answer

What happens when we add an index to an existing table with a large amount of data?

I've a table which will contain around ~15 Million records. Now I need to add an index to the table. Adding an index will take some time to update every entry in the table. I'm quite confused whether adding the index will cause downtime. If yes,…
Surender Thillainathan
  • 263
  • 2
  • 8
1
2 3
…
22 23