Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit 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.

0
votes
0answers
42 views

Perform SELECT without Locking Table but don't allow Dirty Reads

Is there a way I can perform a SELECT query which wont lock the table it is reading but also won't perform dirty reads? for example, assume Table1 has 100m records: SELECT Col1, Col2 FROM ...
0
votes
1answer
26 views

Update Statement on one table takes locks on other tables

Given the following statement on the AdventureWorks database: UPDATE Sales.SalesOrderDetail SET OrderQty = 100 WHERE ModifiedDate >= '2014-01-01' If I open another query window and look at the ...
0
votes
0answers
20 views

Understanding non repeatable read anomaly in databases

This paper discusses in detail ANSI isolation levels discussed in ANSI SQL standard on page 84. Paper introduces following shorthand for describing histories which contain reads, writes, commits and ...
0
votes
0answers
27 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 ...
0
votes
0answers
10 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 ...
1
vote
0answers
17 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 ...
-2
votes
2answers
66 views

Are lost updates completely handled in SQL Server or is manual intervention still required? [closed]

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 ...
0
votes
0answers
20 views

Sharing read/write resources between connections in an unfair way

I have two applications that connect to a Postgresql database. A read-only application querying tuned MVs A write-only application updating the database and the MVs We only really require that (1) ...
0
votes
1answer
58 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 ...
0
votes
1answer
24 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 ...
0
votes
1answer
133 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 ...
1
vote
1answer
21 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 ...
2
votes
0answers
91 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 ...
2
votes
2answers
96 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
1answer
45 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 ...
0
votes
1answer
58 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 ...
3
votes
0answers
68 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 = ...
0
votes
1answer
33 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 ...
1
vote
1answer
53 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 ...
0
votes
1answer
204 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 ...
4
votes
3answers
513 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 ...
0
votes
0answers
31 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 ...
5
votes
1answer
511 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 ...
2
votes
2answers
102 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,...
7
votes
3answers
2k 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 ...
1
vote
1answer
197 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
0answers
140 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 ...
1
vote
1answer
23 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 ...
3
votes
2answers
180 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, ...
0
votes
2answers
86 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 ...
4
votes
2answers
493 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 ...
0
votes
2answers
528 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 ...
1
vote
2answers
197 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. ...
11
votes
6answers
3k 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 ...
8
votes
1answer
2k 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 ...
1
vote
2answers
453 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) ...
-1
votes
1answer
74 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: ...
0
votes
1answer
99 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 ...
3
votes
1answer
371 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 ...
1
vote
1answer
1k 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', ...
1
vote
1answer
371 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
1answer
123 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
0answers
101 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 ...
1
vote
1answer
29 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, ...
0
votes
1answer
37 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: ...
3
votes
1answer
742 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 ...
1
vote
1answer
333 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,...
1
vote
1answer
70 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 ...
0
votes
0answers
324 views

Concurrency with triggers in mysql

I'm trying to implement a hierarchy using closure tables maintained by triggers. I'm using MySQL 5.6. I know we should move out of the 90s and use a database that actually supports CTEs, but this is ...
3
votes
1answer
906 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 ...