The concurrency tag has no wiki summary.
19
votes
4answers
707 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 ...
13
votes
3answers
1k 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, ...
11
votes
1answer
218 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 ...
10
votes
2answers
1k 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?
7
votes
4answers
718 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 ...
7
votes
3answers
901 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
...
6
votes
1answer
101 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 ...
5
votes
4answers
383 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 ...
5
votes
1answer
119 views
How to improve performance on PostgreSQL when using multiple concurrent processes?
Seeing query performance which is not satisfactory on our Python application, which runs several processes that use SQLAlchemy core to access a PostgreSQL 9.2 database. We may have around 100 - 200 ...
4
votes
3answers
2k 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 ...
4
votes
2answers
170 views
Concurrency question?
I have a MySql (5.5) database with a MS Access frontend. If two users are viewing the same record at the same time how can I make each user aware of each other? For example how would I go about ...
4
votes
2answers
3k 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, ...
4
votes
2answers
945 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 ...
4
votes
1answer
225 views
How large should my pool size be? And should I use pgbouncer?
I have a Rails application with 4 Unicorn workers. How do I determine what postgresql database pool size I should use?
And if I have 6 applications connecting to this postgres database, would that be ...
3
votes
2answers
391 views
SQL Server (2012) non-blocking DDL?
I'm not very experienced with SQL Server, so maybe I'm missing something
My situation is as follows:
Session 1 runs a CREATE TABLE (or other CREATE statements) with autocommit off and the CREATE is ...
3
votes
2answers
463 views
Concurrently update with limit mysql
I want to know if concurrently UPDATE .... LIMIT N will not overlap with more than one client querying the db.
After UPDATE ... LIMIT N the client will make a SELECT with some client_id assigned.
I ...
3
votes
1answer
2k views
Oracle 11g hunting down the deadlock, maybe foreign key?
I am trying to understand why a deadlock occurs in my database. The trace tells me that two updates on the same table are locking. In table is the first table to be written to in an transaction, only ...
2
votes
1answer
74 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 ...
2
votes
1answer
50 views
Read-only on an SQLite database which is being modified by another program
I wrote a program that has an SQLite database, modifying it every few seconds.
Now I want to write a small gadget program that would just read a bit of info from that database (read-only) and display ...
2
votes
1answer
59 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 ...
2
votes
1answer
269 views
performance issues when concurrent requests
We are using SQL Server 2008 R2 on a Windows Server 2008 server.
We have some main functions which uses some Stored Procedures which has a lot of sub-queries inside of it.
I'll take one SP as an ...
2
votes
1answer
196 views
unique constraint violated (ORA-00001) in concurrent insert [closed]
I have a procedure that is called from concurrent transactions:
//Some actions here
INSERT INTO table1
(table1_id, table1_val1, table1_val2, table1_val3)
VALUES
...
2
votes
0answers
58 views
How to safely take an SQL job out of the schedule?
There is a table | jobID | status | startTime | endTime | and a job, that is scheduled to run every X seconds, it will take the first undone job ID, put it in stored proc and run it. Stored proc takes ...
1
vote
1answer
601 views
Concurrency with Select-conditional Insert/Update/Delete - PostgreSQL
I have an intervals table which stores a start date and an end date for variables variable_id (four columns : id, start, end, variable_id).
Clients compute new intervals for one variable and store ...
1
vote
1answer
92 views
Can a Database request automatically remove itself from the queue after a delay?
I'm using table locking in my Oracle database. I have a small program with a function that locks my table, does some things and unlocks it again. If many instances are run and requests are made ...
1
vote
1answer
106 views
timestamp based concurrency
I am wondering if someone could help me out with a question I have about time stamp based concurrency.
I found the following practice problem on a website
It basically asks which of the following ...
1
vote
1answer
257 views
Which Optimistic Concurrency Control method will work out best in the long run?
These are the options I've thought of:
DotNet Dave:
UPDATE mytable set f1=newval where id=666 and f1 := f1ValueFromSelect
Charlie Checksum:
UPDATE mytable set f1=newval where id=666 and ...
1
vote
1answer
369 views
Differences between SQL Server 2005 and SQL Server 2008 deadlock handling
We recently upgraded a server to SQL Server 2008 R2. The server had been running SQL Server 2005. We are using
Transactional Replication
Management Data Warehouse and Data Collector on the ...
0
votes
1answer
230 views
How could I handle heavy load and consistency for product inventory in online retailers? [closed]
I have posted that question on Stackoverflow and they suggest me to repost my question here, as it would more likely to get help from a DB community (regarding this issue).
Hope you guys don't mind.
...
0
votes
1answer
74 views
Moving a collection to a new database (v2.2)
To take advantage of the improved concurrency in Mongodb 2.2--namely database level locking--we would like to move a sharded collection to a new database.
I have been unable to find any documentation ...
0
votes
1answer
154 views
Why we use low numbers for concurrent in MySQL benchmarks or HTTP_LOAD? [closed]
I've seen so many times that we use numbers like 5,6 or 10 at most. What is this concurrency? Doesn't it refer to number of simultaneous users in bench?
EDIT
I'm not talking about any specific ...
0
votes
3answers
73 views
Importing data with a lot of SELECTS during transaction
I'm using SQL server 2008 R2 and I have a products table with an auto increment integer id column as primary key and a product_no column (unique) and 6 tables like articles and product_assets which ...
0
votes
1answer
162 views
Show processlist / Max concurrent connections seem to max out at 131
When I put my database under a load test (basically just a basic write and basic read from our app), and run show processlist; in the middle of it, there is always 131 rows in the set and that's it. ...
0
votes
1answer
115 views
MySQL concurrent INSERTs
I have a MySQL database with InnoDB tables.
There are different client processes making SELECT (to check the existence of a value) and INSERT or UPDATE (depending on the result of the select) ...
0
votes
0answers
31 views
Oracle User Profile for REST API
What sort of profile would you recommend for an Oracle user that is manifested through a RESTful API; particularly where there will be a lot of asynchronous requests? So we're talking about a ...
