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)

2
votes
2answers
306 views

Transaction basics. What is the result of 2 transactions running concurrently?

I have a rather basic question on transactions. Assume a table Account with a column amount. Now we have the following code: BEGIN $amount = SELECT amount FROM ACCOUNT WHERE id = 123; $amount = ...
2
votes
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
2k 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
0answers
234 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 ...
2
votes
1answer
231 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 ...
2
votes
1answer
980 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
89 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
2answers
4k 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 ...
1
vote
2answers
346 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 ...
1
vote
1answer
241 views

Improve performance on concurrent UPDATEs for a timestamp column in Postgres

I am using a timestamp column called updated_at for cache invalidation. More information on this particular technique here. The queries all have the same format UPDATE "managers" SET "updated_at" = '...
1
vote
1answer
64 views

Updlock hint in stored procedure

If someone runs stored procedure below very fast (few thousand execution a second), can variable @amount change while executing and execute update more than one time? CREATE PROCEDURE [dbo].[...
1
vote
1answer
99 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 ...
1
vote
2answers
515 views

DB Locks - How would one handle long transactions and maintain high concurrency?

We're maintaining a POS/WMS Application built in powerbuilder that runs on SQL Server 2008 R2 backend. Recently, we've been having a lot of locking issues and I believe it has to do with how we handle ...
1
vote
1answer
136 views

How, when and why does implicit changing of transaction isolation level happen?

RCSI (Read Committed Snapshot Isolation) is the setting of a database changing the behavior of (the default) Read Committed isolation level in SQL Server. This is, as I understand, the context of ...
1
vote
1answer
48 views

How can CREATE OR REPLACE fail with already exists?

DETAIL: Key (proname, proargtypes, pronamespace)=(rand, , 2200) already exists.: CREATE OR REPLACE FUNCTION "rand"() RETURNS float AS How on earth is that even possible? How can CREATE OR REPLACE ...
1
vote
1answer
47 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" ...
1
vote
1answer
2k 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
1answer
341 views

How to insert and or update a variable number of records and prevent concurrency issues

I currently have the following stored procedure. However I am seeing some race conditions where I end up with incorrect data which I think is caused because the procedure isn't attomic and it can be ...
1
vote
1answer
131 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
28 views

Using @@ROWCOUNT in multi-operation batch to check either a failure or a success of the whole transaction

I have the following script defined with hopefully explanations in comments to explain rationale in the right context. The questions are basically: Is the following usage of @@ROWCOUNT in ...
1
vote
2answers
59 views

SQL Server Concurrency Issue [closed]

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
69 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 ...
1
vote
1answer
435 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 ...
1
vote
1answer
44 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
235 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
817 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 md5(...
1
vote
1answer
26 views

Paged query against timestamp populated by trigger missing items

We're having a strange problem with a PostgreSQL 9.2 database that I've been racking my brain trying to figure out. First some background. We have a "tombstone" table that is populated using a ...
1
vote
2answers
88 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 ...
1
vote
1answer
158 views

How does the transactions interact if you set a different isolation level per connection?

I read that I can have different isolation levels per connection and in the server. The default isolation level is REPEATABLE-READ. So if I have a transaction that issues a SERIALIZED to the client ...
1
vote
1answer
3k 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. I'...
1
vote
1answer
2k 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 ...
1
vote
0answers
21 views

Which database supports multi-user write or Concurrent writes [closed]

Can Anybody help me I was using SQLite database but it does not support multi-user write or concurrent writes or simultaneous multiple writes. I want to Ask which reliable Sql or non-Sql database ...
1
vote
0answers
50 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 ...
1
vote
0answers
112 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 (...
1
vote
2answers
1k views

Global Temporary Table used in Stored Procedure executed concurrently across sessions

I use dynamic SQL in a stored procedure and must use a global temp table so the temp table and its data is available outside the instance when sp_executesql happens. I need the stored procedure to be ...
1
vote
0answers
62 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 bigtable;...
1
vote
0answers
1k 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 ...
1
vote
0answers
343 views

High concurrency is even slower than low concurrency in mysql? [closed]

My application is supposed to handle high concurrency insert/update, now I have only one mysql instance setup for test. My application is full asynchronous, it should be able to handle 10,000 ...
1
vote
1answer
73 views

Patterns/Designs to help with concurrency/performance [closed]

I'm working on a SQL Server 2008 R2 DB and I'm experiencing some performance/concurrency issues with probably the main table in the DB. Problems like slow reads, deadlocks, poor performance in general....
1
vote
1answer
274 views

UPLOCK to avoid multiple reads

Given the following table structure... Orders ====== OrderId int PK OrderEvent =========== OrderEventId int PK OrderId int FK EventTypeId int FK I've got multiple concurrent processes SELECT-ing ...
0
votes
1answer
2k views

Why are timestamps not always increasing with concurrent inserts?

I'm seeing some unexpected behavior with timestamp (rowversion) columns . I created a test table: create table Test ( Test_Key int identity(1,1) primary key clustered, Test_Value int, ...
0
votes
2answers
3k views

How to maintain MySQL performance at high concurrency

The following has been observed using "mysqlslap". mysqlslap -u root -p --create-schema=matrix --query=monthly_kpi.sql --iterations=1 --concurrency=1 --delimiter=";" Benchmark Average number ...
0
votes
1answer
1k views

PostgreSQL - If I run multiple queries concurrently, under what circumstances would I see a speedup? Under what circumstances would I see a slowdown?

I approach you all humbly as one who is NOT a DBA, and I'm sure that my question is fraught with conceptual shortcomings and "it depends on" land mines. I'm also pretty sure that all of you who ...
0
votes
1answer
42 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
2answers
141 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
38 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 ...
0
votes
1answer
585 views

How to create fault tolerant databases? [closed]

I have a local application running on a LAN. Sometimes a database gets shutdown. In such a scenario how can I keep the application working? Can I have two concurrent databases so that one picks up the ...
0
votes
2answers
955 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
1answer
504 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
397 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 ...