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
2
votes
1answer
164 views

How the single SQL statement is isolated?

All the books I've seen so far, talking about transactions show scenarios with several SQL statements involved. But what's about single statements? What level of isolation do they have? Is it ...
1
vote
0answers
47 views

Sequence number for COMMITTED concurrent inserts

For example, I have a table create table Queue (OrderNum int, Data varchar(1000)) --int - may be a different type and many concurrent inserts into this table. Is there is a way to fill the OrderNum ...
7
votes
2answers
262 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 ...
2
votes
2answers
202 views

Deletion of orphaned rows in a violation-free & non-blocking manner: predictive order of execution of predicates in case of double-checked locking

I have tables person and toy. There is a foreign key person.favorite_toy_id from person to toy. This foreign key is declared as ON DELETE RESTRICT. I now want to delete all toys which are no longer ...
1
vote
1answer
536 views

mysql/aurora not immediately seeing results of committed transactions

In a system running against an AWS aurora-mysql database, the following (obviously extremely simplified) code runs. It's a highly concurrent system, multiple threads, multiple connections to db. The ...
0
votes
0answers
37 views

Effect on concurrency of breaking queries into multiple statements

Let's say I have a paginated search query which takes a long time (when the offset is large) due to a large number of rows in the joined tables. The purpose of the query is to search through accounts, ...
-4
votes
2answers
272 views

In the following inventory system, is it possible to force row locks to avoid deadlock and ensure it works as intended? [closed]

I want to atomically reserve inventory for N objects for M users. I have two inventory tracking tables, one for global inventory and one for personal inventory. Global Inventory Table Columns: ...
3
votes
0answers
543 views

Grant Privileges on Production Database Throws “Error: tuple concurrently updated”

I would like to grant read access to all tables in my schema GRANT SELECT ON ALL TABLES IN SCHEMA public TO XXX; While it works on our alpha environment, it fails with our production database: [...
1
vote
2answers
359 views

How to handle concurrency during an INSERT-SELECT cycle?

The application should limit each user's access to a certain resource to a maximum number of accesses every 24 hours. To keep track of those, we have the following table: CREATE TABLE accesses ( ...
0
votes
2answers
3k views

SQL statement sequential execution for multiple statments [duplicate]

If a single SQL file contains 2 SQL statements below each other (as below) for millions of records Query 1 : select Id into MyTempTable from TableB b join abc a on a.Id = t.Id Query 2 : Update ...
4
votes
2answers
374 views

Why is our query suddenly returning rows it should not (using READPAST and UPDLOCK options)?

We have a job table that looks like this CREATE TABLE [dbo].[Clearing]( [Skey] [decimal](19, 0) IDENTITY(1,1) NOT NULL, [BsAcctId] [int] NULL, [Status] [varchar](20) NULL, CONSTRAINT ...
1
vote
1answer
259 views

How to prevent concurrency issues in a ssis package?

Have a SQL Server 2017 Server. Have several SQL Server jobs that call a single SSIS package using the SYNCHRONIZED flag. In the package there is a data flow that runs a stored proc (please see below)...
1
vote
1answer
127 views

Deadlock on the same resource calling the same procedure

I would like to kindly ask community for help with this deadlock scenario: <deadlock-list> <deadlock victim="process17392264e8"> <process-list> <process id="...
0
votes
1answer
73 views

Assign foreign keys only after populating database

I am porting a MongoDB database over to a PostgreSQL (relational) database. I would like to perform this task concurrently, and I have developed code with parallelism in mind, but dependencies between ...
0
votes
0answers
1k 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
143 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 ...
2
votes
1answer
554 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 ...
1
vote
0answers
79 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
21 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 ...
-1
votes
2answers
257 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 ...
0
votes
1answer
112 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
118 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
261 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
34 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 ...
4
votes
1answer
258 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 ...
3
votes
2answers
1k 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
162 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
156 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
248 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
164 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
55 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
1k 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
2k 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
42 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 ...
8
votes
1answer
3k 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
589 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,...
14
votes
3answers
10k 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
698 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
316 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
24 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
1k 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
860 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
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 ...
0
votes
2answers
802 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 ...
3
votes
2answers
539 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
5k 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 ...
9
votes
1answer
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 ...
1
vote
2answers
1k 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
84 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
200 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 ...