Skip to main content

Questions tagged [concurrency]

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

Filter by
Sorted by
Tagged with
143 votes
1 answer
180k views

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 ...
vastlysuperiorman's user avatar
41 votes
5 answers
36k views

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 ...
DaveyBob's user avatar
  • 551
36 votes
5 answers
9k views

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 ...
xenoterracide's user avatar
30 votes
6 answers
6k views

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 ...
Fabian Schmied's user avatar
28 votes
3 answers
16k views

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, ...
ErikE's user avatar
  • 4,315
25 votes
2 answers
17k views

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 ...
Amandasaurus's user avatar
24 votes
2 answers
17k views

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's user avatar
  • 1,472
24 votes
3 answers
26k views

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 ...
Elliot Blackburn's user avatar
23 votes
1 answer
18k views

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 ...
Aaron Johnson's user avatar
22 votes
2 answers
43k views

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 ...
Rob Miller's user avatar
17 votes
7 answers
8k views

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 ...
user avatar
16 votes
1 answer
468 views

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 ...
Sergei Namniasov's user avatar
16 votes
2 answers
8k views

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 ...
ruelloehr's user avatar
  • 261
16 votes
1 answer
8k views

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 ...
Surender Thillainathan's user avatar
15 votes
1 answer
8k views

What is "special" about PostgreSQL update vs delete+insert

My understanding is that an update locks a tuple, marks it as deleted, and then adds a new tuple. In other words, update = delete + insert. Or so I had believe. But it appears there something ...
Paul Draper's user avatar
14 votes
3 answers
30k views

How to perform conditional insert based on row count?

I'm using Postgres 9.3, and I need to prevent inserts into a table based on a count of specific rows already in the table. Here's the table: Table "public....
Rob Johansen's user avatar
14 votes
1 answer
30k views

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 ...
Songo's user avatar
  • 293
13 votes
3 answers
2k views

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, ...
alice's user avatar
  • 477
13 votes
2 answers
16k views

Drop an index in postgresql

I had created an index wrongly and now I am trying to drop that index. Since the table is large, dropping the index is taking lot of time. Is there any other way to drop the index quickly?
user avatar
12 votes
2 answers
20k views

Why am I getting a deadlock for a single UPDATE query?

I have two processes which execute code like this in parallel: begin; update foos set unread=false where owner_id=123 and unread=true; commit; This results in deadlocks. My understanding of what ...
John Bachir's user avatar
12 votes
1 answer
6k views

Why is CTE open to lost updates?

I don't understand what Craig Ringer meant when he commented: This solution is subject to lost updates if the inserting transaction rolls back; there's no check to enforce that the UPDATE affected ...
Gili's user avatar
  • 1,029
12 votes
3 answers
7k views

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 ...
Chris's user avatar
  • 303
12 votes
1 answer
502 views

Highly Concurrent Storage System

Imagine your requirement is that you have 3 huge tables (structured data) with say 30 billion rows in each (total size of 4TB) and your many concurrent users (which are parallel os threads on remote ...
iCode's user avatar
  • 223
11 votes
2 answers
22k views

Using multiple cores for single MySQL queries on Debian

I am running a MySQL server for tests on a VM (VMWare) with Debian as guest OS. The guest has four emulated CPU cores, so I set thread_concurrency to four. I am doing expensive joins on large tables, ...
Thomas's user avatar
  • 307
11 votes
2 answers
4k views

LATCH_EX Waits on Resource METADATA_SEQUENCE_GENERATOR

We have a process that generates an inventory report. On the client side, the process splits of a configurable number of worker threads to build a chunk of data for the report that corresponds to one ...
Paul Williams's user avatar
11 votes
1 answer
13k views

PostgreSQL concurrent increment of counter

I need to maintain a statistical table for a project, composed by a list of items and their usage (Think about something like a website in which you would like to count pageviews). Each time an item ...
eng.student's user avatar
10 votes
1 answer
3k views

Isolation and atomiticy of SQL Server UPDATE

Is there any difference in atomicity and isolation between the two following SQL statements? Batch A: UPDATE Orders SET InProgress = 1 WHERE Completed = 0 and Batch B: UPDATE Orders SET ...
JoeCool's user avatar
  • 233
9 votes
2 answers
2k views

UPDATE statement behavior

I have a question regarding inner workings of UPDATE statements with regards to SQL Server. I am trying to understand what will happen if the following 2 update statements are received or serviced at ...
Vivek Kumar Batra's user avatar
9 votes
4 answers
770 views

Resources for understanding SQL Server locking and concurrency?

As demonstrated by a recent question of mine locking and concurrency are HARD. Can you suggest any good resources for intermediate-to-advanced SQL professionals to do a thorough study on these that ...
ErikE's user avatar
  • 4,315
9 votes
2 answers
3k views

Safe to use WITH (NOLOCK) in this case?

Scenario I have a table that deals with concurrent SELECT's and DELETE's. I'm getting a few deadlocks on my SELECT statements. I assume that the DELETE from the other transaction is getting an ...
trademark's user avatar
  • 213
9 votes
1 answer
4k views

Finding the default isolation level in SQL Server 2017 and Azure SQL DB

I'm reading a book related to transaction and concurrency. In one paragraph it is mentioned that: In on-premises SQL Server instance the default isolation level is Read-Committed based on locking ...
user avatar
9 votes
4 answers
3k views

Strategy for concurrent group bookings?

Consider a seat booking database. There is a list of n seats, and each one has an attribute is_booked. 0 means it isn't, 1 means it is. Any higher number and there is an overbooking. What is the ...
Benjamin Scherer's user avatar
9 votes
1 answer
17k views

SQL server temp table using # - is it only accessible by the containing query for multiple connections with the same login?

Say we have 2 completely different queries, that reference a temp table with the same name: query one ....operates on: #tempTableName query two ....operates on: #tempTableName I've ...
Chris Halcrow's user avatar
9 votes
1 answer
19k views

How to view full blocking postgres queries?

I'm using a statement from this page to view blocking queries in Postgres SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, ka.query ...
Tony's user avatar
  • 579
8 votes
1 answer
6k views

How is SQL Server returning both a new value and old value during an UPDATE?

We've had issues, during high concurrency, of queries returning non-sensical results - results the violate the logic of the queries being issued. It took a while to reproduce the issue. I've managed ...
Ian Boyd's user avatar
  • 976
8 votes
1 answer
3k views

Does UPDLOCK guarantee concurrency

I have a table called tblOrderNumber which has 1 row and 1 column. This table stores what the next order number will be for my ecommerce website. It is ABSOLUTELY VITAL that the same order number is ...
user1786107's user avatar
7 votes
1 answer
3k views

What are the problems with using NOLOCK given an approach to handle the short comings?

I am researching the harm of using NOLOCK SQL to load data from one database that is actively used into a reporting database. I understand that there are problems with using NOLOCK but I am thinking ...
variable's user avatar
  • 3,476
7 votes
2 answers
5k views

Performance of large transactions and concurrency?

If I have a multi-million row table and I run a transaction that updates 50k rows, what are the performance implications of this? Assuming it's indexed correctly, it shouldn't take long, but what ...
Alexander Kleinhans's user avatar
7 votes
3 answers
12k views

Concurrent MySQL updates hang with InnoDB (on Amazon RDS)

I'm running into a problem where multiple MySQL updates being performed at the same time will lock up and takes several minutes to finish. I'm using InnoDB, so I'm confused as to why this could be ...
Alan's user avatar
  • 205
7 votes
1 answer
636 views

Missing rows under read committed isolation

It's well known that Read Committed isolation is prone to different anomalies. I read The Great Paul White's series on isolation levels. The post relevant to the discussion is this one: The Read ...
Alex Shelemin's user avatar
7 votes
4 answers
966 views

Is my queue table implementation race condition safe?

Hello people smarter than me! I've created a sort-of-a-queue table system, but it seems too simple to be safe from race conditions. Am I missing something or is the following race condition safe? The ...
J.D.'s user avatar
  • 38.3k
7 votes
2 answers
494 views

Does timestamp protocol following thomas's write rule allow non-view-serializable schedules in some cases?

I have come across the following line in a text book (Database System Concepts Textbook by Avi Silberschatz, Henry F. Korth, and S. Sudarshan $6e$) page no. 686: Thomas’ write rule allows schedules ...
Vimal Patel's user avatar
7 votes
2 answers
19k views

Way to do MERGE with update source when target not match?

I am trying to write a stored procedure that will take new values from a temp table, merge them into an actual table, giving me the ids of the inserted rows, and the rows that already exist matching ...
tostringtheory's user avatar
7 votes
2 answers
509 views

Using a Table as a Queue without sp_getapplock/sp_releaseapplock

I have a list of commands I need to execute, all of which are contained within a table I've named myQueue. This table is a little unique in that some commands should be grouped together such that ...
John Eisbrener's user avatar
6 votes
2 answers
14k views

SQL Server Concurrent Inserts and Deletes

From: SQL Server 2014 Concurrent input issue Follow up question: How do we Delete and Reinsert rows in a parallel, multithreading environment while avoiding racing conditions, deadlocks, etc? Do we ...
user avatar
6 votes
2 answers
3k views

Will my SQL query use stale data? How can I prevent it?

I have two tables (SJob & SJobDependent) that I need to join for some logic in a stored procedure. They both have a column (job) that connects them in a one-to-many relationship - one SJob record ...
Max Jacob's user avatar
  • 163
6 votes
2 answers
7k views

Concurrent Transactions vs Simultaneous Transactions

Chapter 6 of David Kroenke's Database Concepts: Database Administration Is my understanding correct that in a database, simultaneous transactions don't really happen. Instead, it is a concurrent ...
momo's user avatar
  • 61
6 votes
1 answer
3k views

How to atomically replace a subset of table data

In PostgreSQL 9.6 I have a table T like this category | id | data ---------+----+------ A | 1 | foo A | 2 | bar A | 3 | baz B | 4 | eh B | 5 | ...
vektor's user avatar
  • 401
6 votes
1 answer
2k views

Any reason not to add 'rowversion' column?

The TechNet article, "Optimizing Microsoft Office Access Applications Linked to SQL Server", recommends adding a rowversion column to SQL Server tables linked from MS Access. From the Supporting ...
mwolfe02's user avatar
  • 432
6 votes
2 answers
640 views

Tuning Postgres for Single connection use? Or is postgres wrong tool?

Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions? I'm a social ...
nick_eu's user avatar
  • 213

1
2 3 4 5
7