Questions tagged [concurrency]
For questions about issues arising when multiple users or processes concurrently access or modify shared information in a database system.
309
questions
2
votes
1
answer
2k
views
Understanding repeatable read isolation level
Wikipedia defines following terms:
With a lock-based concurrency control DBMS implementation, serializability isolation level requires read and write locks (acquired on selected data) to be ...
- 219
1
vote
0
answers
126
views
Understanding connection between different database concurrency control protocols
I have came across following diagram related to timestamp based concurrency control protocols in database systems (as summarized in these sldes or detailed in the book by Korth et al.):
I don't know ...
- 219
1
vote
0
answers
26
views
Understanding what does it mean when we say certain rule ensures conflict serializabiliy
Database book by Korth explains validation protocol as follows :
To perform the validation test, we need to know when the various phases of transactions took place. We shall, therefore, associate ...
- 219
-1
votes
2
answers
556
views
Are lost updates completely handled in SQL Server or is manual intervention still required?
As lost updates occur when 2 different transaction try to update the same thing at the same time - different sessions update the same row; the last one overwrites the changes made by the first.
We ...
- 2,098
0
votes
1
answer
382
views
Ensuring unique value for group of rows in table with trigger and concurrent transactions
I've seen similar variations of this question asked, but nothing that tackles this particular implementation and I was wondering if it was possible to do with sql/plpgsql. Take this as more of a ...
- 113
0
votes
1
answer
265
views
Will Postgresql statements execute synchronously if autocommit=true?
If a single application thread sends a series of INSERT statements and autocommit=true, will the statements be executed synchronously on the server?
I read that on the postgres server side, "there ...
- 3
0
votes
1
answer
327
views
Concurrent update queries locking rows when `where` clause doesn't match
My application sends messages to Slack. On very rare occasions we may need to retract the message going out. These retractions are handled by queueing a job for each messageId that has been created ...
- 215
1
vote
1
answer
122
views
Database transaction consistency on PostgreSQL
In a table with three columns (serial id, varchar name, varchar email) and having two different transactions executing concurrently as the ones bellow:
Transaction 1:
BEGIN;
INSERT INTO public.users ...
- 11
4
votes
1
answer
467
views
Is it best practice to use surrogate keys when creating foreign key constraints in SQL Server?
I'm trying my best not to ask a leading question, but after a lot of searching I'm unable to find a similar answer or recommendation.
First, take a look at this question I asked on Stack Overflow ...
- 213
7
votes
2
answers
3k
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 ...
2
votes
1
answer
243
views
Concurrent requests not running in parallel
I am trying to run update statistics in parallel.
Say I start with 2 sessions: S1, S2
In S1 I run
Update Statistics A with fullscan and time it to take 2 minutes.
In S2 I run Update Statistics B ...
- 21
0
votes
1
answer
252
views
Concurrent backups in MS SQLServer 2008 R2
Can anyone please point me to a piece of documentation that suggest how many concurrent backups initiated by different BACKUP statements are possible in Microsoft SQL server 2008?
The documentation ...
- 143
3
votes
0
answers
329
views
Why do two transactions making a read lock result in a DEADLOCK exception?
Let's assume a scenario that flows as follows:
Transaction #1 makes this query:
SELECT v.id, v.name
FROM versions_history as vh
INNER JOIN versions as v
ON vh.version_id = v.id
WHERE vh.project_id = ...
user162550
0
votes
1
answer
353
views
Multiple concurrent sql dumps in mysql
My question is whether it is safe to perform multiple dumps at the same time in mysql, i.e. running:
mysql -u root -p database1 < database1.sql
mysql -u root -p database2 < database2.sql
on ...
- 191
1
vote
1
answer
66
views
Isolation level issue
I need some help in choosing the correct isolation level on my transactions.
My situation is as follows. I have a table with a unique constraint over two columns. For the sake of the argument, lets ...
- 173
0
votes
1
answer
2k
views
How to design a database-Excel connection for read-write by multiple users? [closed]
In my project users maintain data in Excel tables (with proper row and column
headers). With time however, the Excel tables get pretty large.
The amount of numerical data is not too large (~10mb per ...
- 139
4
votes
3
answers
3k
views
Does MERGE prevent deadlocks and server blocking?
I have started looking at MERGE as an option that I would use in my application to process UPSERT transactions. Seems that a lot of SQL Server experts are recommending this approach. However, while ...
- 165
0
votes
0
answers
56
views
Setting up a Database for Multiple User Access
I am working on revamping the database at the company I work at. I've been making decent progress getting various parts set up, but something occurred to me today that I hadn't really thought to ...
- 381
12
votes
2
answers
6k
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 ...
- 221
2
votes
2
answers
820
views
Why is column value going negative even with checks
I have a simple MySQL table with 3 columns id, balance and locked. I have a requirement where the locked column's value must be increased by x, sometimes by multiple processes running at the same time,...
- 131
22
votes
3
answers
20k
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 ...
- 325
1
vote
1
answer
1k
views
Sporadic Gap-lock related Deadlocks under high concurrency in MySQL 5.7
Under high concurrency when running a daily process overnight we see deadlocks occur on a particular table, which seems to reference that it was holding a gap lock at the time. However I'm unable to ...
1
vote
0
answers
420
views
MongoDB write locks on insert
Why would an insert into a MongoDB 3.4 collection require global write locks? I'm creating a copy of a large collection in a new replica set (via mongodump/restore) and I'm finding that some inserts ...
- 135
1
vote
1
answer
25
views
How to concurrency will be handled in this case?
Say I have employee table with 100 records . Out of 100 records, 60 records having middle_name as null
Say in two separate sessions(say Session_A, Session_B), I execute below query at same time
...
- 389
3
votes
2
answers
2k
views
MySql concurrency with COUNT
I Have a table with user_id and product_id.
For my purpose, a particular user is allowed up to 5 rows only.
To enforce this, I created such a SQL statement:
INSERT INTO the_table (product_id, ...
- 83
1
vote
2
answers
3k
views
Is it possible to make a SELECT wait until an ongoing UPDATE finishes?
Suppose that I am implementing a bank account logic using PostgreSQL. At some point, I will want to check if the user has sufficient balance to perform an operation. However, the user's balance might ...
- 229
4
votes
2
answers
1k
views
Contention on tempdb due to high concurrency temp table creation from multiple databases?
I have an environment with ~100 databases, all with the same schema. There is a stored procedure on each database that creates a #temp table and drops it, and is running very often (every 30 seconds ...
- 389
0
votes
2
answers
1k
views
the role of " Intent lock " in transaction and concurrency
According to the book Microsoft Sql server 2012, Chapter 13 (Transactions and concurrency):
If an exclusive page lock is held in one page of a table, another
process can not get even a shared ...
user123215
3
votes
2
answers
868
views
SQL Read Committed Snapshot Isolation in Database with Selects and Inserts Only
We have database with only 1 table, which conducts Inserts and Selects.
There are No Updates in the table.
Table has lot of Concurrency; with many simultaneous Insert and Select queries occurring. ...
user129291
15
votes
6
answers
6k
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 ...
user123215
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
...
user123215
1
vote
2
answers
2k
views
SQL Difference Optimistic Reading and Optimistic Writing
What is the difference between optimistic read and optimistic write?
Or probably more so, can someone define these 4?
(a) Optimistic Reading
(b) Pessimistic Reading
(c) Optimistic Writing
(d) ...
user129291
-1
votes
1
answer
100
views
SQL Snapshot Isolations Phantom Rows
Does Read Committed Snapshot Isolation prevent phantom row issue? Does Snapshot Isolation prevent phantom row issue?
I am seeing different results in researching online:
Akira Yamamoto states: ...
user129291
0
votes
1
answer
270
views
SQL Server Simplify Racing condition query
We import multiple records per second from different flat files. Sometimes we encounter a racing condition, and duplicate Unique error constraint. We are inserting and retrieving records,
I wanted to ...
user129291
4
votes
1
answer
3k
views
SQL Server Concurrent Updlock Serializable vs Try Catch
We import multiple records per second from different flat files. Sometimes we encounter a racing condition, and duplicate Unique error constraint. We are inserting and retrieving records,
I hear ...
user129291
2
votes
1
answer
2k
views
UPDATE with subquery LIMIT 1 returns multiple rows [duplicate]
I am struggling with a query which is randomly returning multiple rows instead of one. I am using PostgreSQL 9.4.12
The query is the following :
UPDATE folder as f set locked = 'locked', ...
- 23
2
votes
1
answer
942
views
How to create some GIN index concurrently in Postgresql
I have a large table: CREATE TABLE hh(h int8[] not null, file int8 not null), and GIN-index over h field (CREATE INDEX ON hh USING gin(h)). Index was created is about 8 minutes, so I decide to split ...
0
votes
1
answer
249
views
Does Write-Ahead-Logging allow me to make concurrent changes to a table?
In Sqlite3, Write-Ahead-Logging mode allows one process to read a table, while another is writing to it. It doesn't say anything about one process writing and another deleting/updating.
Is this ...
1
vote
0
answers
119
views
conditional UPDATE race conditions
In a conditional UPDATE like this:
UPDATE MY_TABLE
SET my_coulmn = "my value" -- many other updates
WHERE ... -- many conditions
Do I have any guarantee that I will only update those records ...
- 111
1
vote
1
answer
40
views
Concurrent Users and Foreign Keys
For this question, please assume the following:
I am using MySQL
I have a Location table: Location(ID, Name, Address, TravelDetails, ...)
I have an Event table: Event(ID, Name, LocationID, StartTime, ...
- 21
0
votes
1
answer
49
views
Changes made by TransactionA not seen by TransactionB eventhough TransactionA commits them. How is that possible?
Is it possible for 2 non concurrent connections to see the same data for a record even as the 1st connection commits its changes?
Here is an example of what I am seeing from the audit logs:
...
- 121
4
votes
1
answer
2k
views
Concurrent UPDATE deadlock (updating 2 fields, selecting by PK)
I have a strange deadlock case, where simple concurrent updates are causing deadlocks on MySQL (5.6.27).
The setup is with multiple workers (several different servers) processing queues and sometimes ...
- 141
2
votes
1
answer
660
views
Transaction log records in sql server
Based on Silberschatz's Database system concepts book, log file for a transaction has 3 records,
1 record for transaction start: T_i start,
1 for transaction changes on write(x) operation: T_i,X,...
user139992
1
vote
1
answer
90
views
Read after transaction from secondary session not visible
We've got a database (MySQL ) that has a client 'per channel'.
When a player changes channel from, say, 1 to 2, we have to save the state of the player to the database.
Saving is done in multiple ...
- 111
3
votes
1
answer
2k
views
SQL Concurrent Inserts and Automatically Find Active Flag Record
What is the proper database design to Instantaneously find Active Flag for an OLTP table: with many rows being inserted into a parallel, multithreading environment while allowing optimization, and ...
user129291
3
votes
1
answer
848
views
SQL Server Concurrent Delete/Insert Application
How do we delete and then insert rows in a parallel, multithreading environment while avoiding deadlocks? I am still receiving deadlocks after applying UPDLOCK and SERIALIZABLE. See deadlock error ...
user129291
0
votes
0
answers
2k
views
AUTO INCREMENT concurrency issue and ordering
Let's suppose that table t1 has an AUTO INCREMENT primary key id. And let's say that I am starting two transactions at different moments of time, and that transactions are started by the same ...
- 193
6
votes
2
answers
13k
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 ...
user129291
0
votes
1
answer
304
views
SQL Server Concurrent Insert/Update and Storing Data
From: SQL Server 2014 Concurrent input issue
Follow up question:
Table Orderline stores information we receive from shops. OrderLineID is the unique, primary key in the table, since orders have ...
user129291
5
votes
2
answers
2k
views
SQL Server 2014 Concurrent input issue
In table Orders, I store the orders we receive from all shops. Since an order can have multiple lines, among the columns there are OrderID and OrderLineID
where OrderID can be duplicated but ...
- 359