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

learn more… | top users | synonyms

1
vote
2answers
57 views

SQL Server Concurrency Issue [on hold]

We have a SQL server concurrency issue we are facing and would like some ideas on how to solve it. There are several procedures we have written the contain INSERT/UPDATE statements on tables in a Set ...
1
vote
1answer
63 views

PostgreSQL optimistic reads (equivalent to SQL Server's Read Committed Snapshot)

Does PostgreSQL provide a built-in optimistic concurrency control mechanism such as SQL Server's Read Committed Snapshot Isolation? Readers can read all the rows without blocking, but writes will ...
0
votes
0answers
15 views

How to lock tables in a stored program if I can’t say LOCK TABLES? (in an ancient version of MySql)

MySQL does not allow LOCK TABLES in stored programs. But this is my problem: I have a large stored function to control “phases”. The function advances to next phase, if needed (rarely so), and ...
0
votes
0answers
14 views

PostgreSQL - Are CREATE FUNCTION and DROP FUNCTION locking when used inside different transactions?

I'm having a locking issue on PostgreSQL 9.4.5. I run two or more transactions simultaneously (generally there are 2 running at the same time, but 2+ may happen sometimes) reading from the same tables ...
6
votes
1answer
350 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 ...
7
votes
1answer
145 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 ...
1
vote
0answers
27 views

How to make a PostgreSQL trigger thread-safe

I want to make my PostgreSQL trigger function do something like incrementing counters etc (something complex with multiple queries etc) thread-safely (so the same function triggered concurrently won't ...
3
votes
1answer
85 views

Why am I getting old data?

Today I discovered something weird on PostgreSQL 9.5. (I have no idea whether this is because of beta or not.) When I want to fetch data, I get old and deleted data from query. I then do VACUUM FULL ...
0
votes
1answer
29 views

What does this deadlock report from MySQL mean?

Here is the output: ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2015-12-23 05:57:47 7f23d73c6700 *** (1) TRANSACTION: TRANSACTION 32990178, ACTIVE 0 sec inserting mysql ...
0
votes
0answers
51 views

How to determine if a schedule is non serializable?

How to determine if a schedule is non serializable? I am aware that a schedule is serializable if there is a concurrent schedule which gets an output same as a serial schedule. But how do you ...
0
votes
0answers
28 views

Understanding Serializeable vs conflict-serializable

I am having hard time wrapping my head around this concept of serializable. I know that if a schedule is conflict-serializeable then it is a conflict-equivalent to some serial schedule. But then does ...
4
votes
2answers
81 views

Best practice regarding concurrency for INSERT into a table with composite primary key?

Say I've got the tables users, teams and a teams_users junction table (team_ID, user_ID / composite PK). If I wanted to add a user to a team, what's the best option when it comes to performance / ...
1
vote
1answer
48 views

Can sequential queries result in concurrent transactions?

I'm using PostgreSQL 9.4.4 and the default configuration. I use a JDBC connection to run multiple queries in succession. Only one Java thread is using the connection and before a new query is executed ...
0
votes
2answers
100 views

How to manage concurrencies without locking tables in MySQL?

In our php app, we have a table that records orders recieved at the reception. Since we can have up to 7 agents/active accounts, how is it possible to manage the concurrencies in the orders table ...
0
votes
1answer
23 views

concurrent insertion in mutually exclusive tables in oracle

There are three tables: create table C(id_row integer primary key) create TABLE A(col_a1,col_a2,col_a3,..., id_row integer primary key, constraint fk_a_c foreign key (id_row) references C(id_row)) ...
4
votes
2answers
971 views

How to avoid a “duplicate key” error?

This statement: INSERT INTO deleteme SELECT #t.id, 'test' FROM #t LEFT JOIN deleteme ON deleteme.id = #t.id WHERE deleteme.id IS NULL; ...will fail with a primary key violation in the concurrent ...
0
votes
0answers
57 views

MYSQL- concurrency issue

I use JDBC to connect to MySQL. order_items table order_item_id order_id quantity unit_price shipping_price business_id workflow_id delivery_id item_id Orders table billing_address_id ...
1
vote
1answer
31 views

PostgreSQL visibility of records changes from other transaction inside the current one

I have a table "tst" which has a "status" column using which we process records in that table. Once the records are processed we set the status to 'Y'; #\d tst Table "core.tst" ...
0
votes
0answers
52 views

Is it a good idea to have too many named locks?

I am using MySQL as a data store and a Java App on the top. There are many state changes that happen, and I am seeing some concurrency bugs in there. I wanted to know if I can use named locks ...
5
votes
1answer
163 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 ...
2
votes
1answer
237 views

Does PostgreSQL support online schema modification (DDL)?

Recent versions of MySQL (using InnoDB) support many alter table queries without exclusive locking the table. Does PostgreSQL support this to any level? That is, can I add/drop columns from a table ...
3
votes
1answer
213 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 ...
0
votes
1answer
187 views

How to execute two transactions concurrently in PostgreSQL?

I want to execute two transactions concurrently in pgAdmin: --The first transaction should modify the age of Paul BEGIN; DECLARE newage INTEGER; --the original age of Paul is 32 newage := Select age ...
1
vote
1answer
227 views

List MATERIALIZED VIEWs in PostgreSQL that do not have a UNIQUE index

Postgresql 9.4 allows to REFRESH materialized views CONCURRENTLY, if they have a UNIQUE index. I am looking fo a SQL-query that lists all materialized views that DO NOT have such a UNIQUE or PRIMARY ...
0
votes
1answer
43 views

How to serialize on SQL server metadata?

I tried to find an answer in Kalen Delaney's SQL Server Concurrency but there is no mention of metadata. The problem is how to ensure that only one of these reads and index creates executes from one ...
1
vote
2answers
71 views

Does InnoDB allow adding columns to a table with concurrent READ/WRITE of rows?

I have a large (3M rows) InnoDB table in MySQL 5.6.21 in production and I want to add some columns. From the MySQL Documentation on Overview of Online DDL I understand that, even if this is an ...
2
votes
0answers
175 views

SSIS: FTP task - concurrent connections

I have created an SSIS package for downloading file from FTP server. When I debug the package, everything goes fine. But when I shedule the package as Job step, it sometimes fails to connect. The job ...
3
votes
0answers
17 views

Lock in mysql database [duplicate]

My schema 'test' has 3 tables: 'user', 'post' and 'revision'. CREATE TABLE IF NOT EXISTS `test`.`user` ( `iduser` INT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NOT NULL, PRIMARY KEY ...
0
votes
0answers
33 views

Can a slow SELECT statement in READ COMMITTED isolation level read content from concurrent committed UPDATE?

Let's say we have a big table with hundreds of million of rows (or more). On one connection, we perform a query requiring full table scan under READ COMMITTED isolation level: SELECT * FROM ...
0
votes
1answer
46 views

InnoDB index concurrent inserts

I have an InnoDB table with an index on it. I have a few million rows to insert, so I thought I might take advantage of the row-level locking and do it in parallel. I'm inserting in batches of 10000, ...
4
votes
1answer
155 views

How to correctly use PostgreSQL to limit multiple and/or concurrent executions of a task

Let's assume you have a task FOO that can be queued once every minute, and a pool of 50 workers that can be paused. The queue is paused for 10 minutes, and 10 FOO tasks are queued. When the queue is ...
1
vote
0answers
538 views

How to identify concurrency issues in Oracle 11g?

For a short period in time we had a concurrency issue that lead to subsequent faults in our application (Java, Spring, Hibernate). Since we are using dictionary managed tablespaces, I first thought ...
11
votes
1answer
294 views

Isolation of SQL Server UPDATE

Let say I have two queries: -- First session UPDATE Person SET Name='Jonny', Surname='Cage' WHERE Id=42 -- Second session SELECT Name, Surname FROM Person WITH(NOLOCK) WHERE Id > 30 The ...
0
votes
1answer
68 views

How to avoid duplications using a function

I am using this function in a bulk insert to avoid duplication of url paths. CREATE OR REPLACE FUNCTION "univ"."gc_landing"(IN _name text, OUT landing_id int4) RETURNS "int4" AS $BODY$ DECLARE ...
3
votes
1answer
191 views

Work queue with complex select and long processing: Ensuring concurrency

I have a queue table from which worker processes claim records, one at a time. It is crucial that no two workers claim same item to process. Normally this kind of thing is done with select top (1) ...
1
vote
2answers
1k 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 ...
1
vote
2answers
2k views

Return the id after insert or select

I want to build a function which will insert an email if the email value doesn't exist in the table and return the email_id of the row. How can I do this? Also how can I return the id if the email was ...
0
votes
1answer
340 views

Concurrent SELECT and UPDATE in multiprocessing python

I have to randomly match about 20 million rows in two tables without using the same row twice using PostgreSQL 9.3.5. Both tables have the following structure CREATE TABLE de_sim_points_end ( -- ...
2
votes
1answer
387 views

Do MongoDB's “read uncommit” and “write lock” conflict with each other?

I was reading the MongoDB documentation, where it says about read uncommit: MongoDB allows clients to read documents inserted or modified before it commits these modifications to disk, regardless of ...
0
votes
1answer
1k views

Oracle Enterprise Manager - top activity

I'm looking at the Top Activity view in OEM. The left axis is labeled "Active Sessions" (up to 24 in the graph I'm looking at) and there is a line labeled "Maximum CPU". The measured activities ...
0
votes
1answer
58 views

Running identical queries in sysbench

When benchmarking using sysbench and utilizing concurrency (multiple threads) for example on a table with 1,000,000 records, does sysbench run "identical" queries in a loop on the table?
0
votes
0answers
82 views

How can I obtain concurrent deletes for this table?

We have a program that is currently doing something to the effect of: SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION UPDATE [Source].[dbo].[SourceTable] SET [ProcessedOn] = ...
1
vote
2answers
290 views

ACID violation in Postgres 9.1?

I am using a postgres DB to implement a scheduling of jobs for a large number of computers/processes. To make the story short, every job has its id, all scheduling is implemented with three tabes: all ...
-2
votes
1answer
52 views

Oracle Concurrent Insertion

We have simple case like Create table t1 (c1 number primary); session1: insert into t1 values (1); //session1 do not commit transaction session2: insert into t1 values (2); Will Session2 ...
6
votes
1answer
2k 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 ...
2
votes
2answers
751 views

Is SQL Server Thread Safe?

If I need to update same row with two concurrent thread then is it possible in SQL Server? Suppose I have one table that contains Id and Name columns and now I want to update the third number Id with ...
0
votes
1answer
37 views

Is reading a single record always an atomic operation?

Is reading a single record always an atomic operation? I mean, when e.g. a read (select) and write (update) query are simultaneously carried out on the same record, is it possible for the read ...
1
vote
1answer
42 views

Is it possible that records are changed while a read query is still executing?

Suppose a read (select) query R is reading some 100 records from a table, 50 records are read so far, could in the meanwhile a write query W change some of the unread records? We didn't take any ...
1
vote
1answer
1k views

MySQL consistent nonlocking reads vs. INSERT … SELECT

So, from my reading of the MySQL 5.5 documentation and some experiments I did with colleagues, I understand that the following is the case: With InnoDB tables, a simple SELECT statement (one that ...
2
votes
1answer
203 views

Does READ UNCOMMITTED have the same effect as AUTOCOMMIT=1?

AFAIK running transactions as READ UNCOMMITTED means that there is no lock, so every change caused by a transaction is immediately visible to other transactions. Is it correct to assume that this is ...