A mechanism for committing a consistent set of changes into a database atomically.

learn more… | top users | synonyms

0
votes
0answers
27 views

Transaction Identifier - possible with MySQL?

I have quite complex code, which at the beginning starts transaction. After that several user queries are performed (more or less without my control) and at the end the transaction is committed if ...
3
votes
1answer
32 views

SQL 2012 CLR error: “The context transaction which was active before entering …”?

I am experience strange error with SQL 2012 and CLR: The context transaction which was active before entering user defined routine, trigger or aggregate "My routine" has been ended inside of ...
1
vote
1answer
255 views

Why are SELECT queries in sleeping status holding open transactions

Using sp_WhoIsActive, I see that I have a lot of processes in a "sleeping" status, several for hours. I am not sure of the cause and am still troubleshooting (timeout? bad code with uncommitted ...
8
votes
2answers
5k views

Deleting MySQL table with pending transactions

Is there a way to delete an InnoDB table or database with pending transactions in MySQL (preferably on file system level)? What happened: I use MySQL 5.5.28 and ran LOAD DATA INFILE… to import a ...
2
votes
1answer
121 views

Write-lock a whole table during transaction

I need to perform a delicate operation to my table in which I will solely insert, delete and select upon all of my rows and no God may interfere with the table during this operation: the table will be ...
2
votes
2answers
115 views

Is there any reason not to use foreign keys in MySQL?

I've never really used foreign keys before but I am building a project where they could come in handy. I have looked at some other large software such as some forum systems and they don't use foreign ...
0
votes
1answer
508 views

MySQL: Transaction and Deadlock

I have table "tbl_data", where have a lot of rows (3 - 5 million). I have JAVA (*.jar) module from which I INSERT/UPDATE data into this table. If I start *.jar file (for example, "run.jar") and after ...
0
votes
0answers
40 views

Stored procedure vs transaction anomaly

We have a stored procedure which updates reporting data in a table. We call this via Hibernate from Java. Within the stored procedure the current time is taken from the NOW() function. The anomaly ...
4
votes
1answer
49 views

Why are transactions running as distributed (DTCXact)?

I am actually trying to analyze and solve some regularly occurring deadlocks that all include at least one transaction named "DTCXact", this makes me ask. I repetitively run the following query ...
2
votes
1answer
257 views

SQL Server Log Shipping Randomly Stops Copying and Restoring

I'm fairly new to Log Shipping but have configured a few with no issues, however I have a server sitting on SQL 2008 R2 with 6 databases on there, all 6 of these are supposed to be log shipping. The ...
1
vote
1answer
41 views

Best way of order processing

I would like to write a procedure for order processing Single Order can have multiple items, how can I process the order ? Consider below JSON is an example: { "Order": { ...
1
vote
1answer
144 views

Print, transaction and cursor performance in Transact-sql

I'm having some performance issues when I run a script. I was reading about the problem with print and its performance, but I cannot figure out how to reduce the impact. The algorithm is O(n^2). The ...
2
votes
1answer
129 views

What is mean by a blind write in a schedule?

I know that every conflict serialisable schedules are view serialisable but the converse is not true. I read in a webpage that when a schedule is view serialisable but not conflict serializable then ...
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
2answers
81 views

list of batch aborting errors in SQL server

In SQL Server, if XACT_ABORT is off then some errors will terminate the current statement (for example supplying the incorrect number of parameters to a stored procedure that takes some parameters) ...
2
votes
1answer
30 views

Basic transactional DDL script in PostgreSQL

PostgreSQL newbie here trying to put together a transactional DDL script to atomically create a database and its schema in Postgres 9.3: in a transaction, create a few tables; if any errors, roll the ...
2
votes
2answers
65 views

Forcing MS SQL Server to rollback on error

I am having some problem to understand how transactions work on SQL Server, even after I thought I had all answers from the documentation. To put it simply, I have a transaction at the beginning of ...
1
vote
2answers
459 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 ...
0
votes
1answer
236 views

How to calculate MySQL Transactions per second

How can I calculate MySQL Transactions per second. I tried to use formula (global com_commit+global com_rollback)/uptime since flush. I had also tried (global insert+ global delete+ global ...
4
votes
1answer
48 views

Persistent inserts in a UDF even if the function aborts

I have a rather complicated UDF (moves around a bunch of tables as well as creates a bunch of new ones) where multiple aborts may occur. Before every operation I would like to record the time when an ...
3
votes
3answers
143 views

Difference between commit and checkpoint

I was confused about the difference between a commit operation and a checkpoint. I referred to some of the articles like This Article But they are advanced for my understanding. I mean both appear to ...
0
votes
0answers
17 views

Classification of strict two phase locking

According to the definition of strict two-phase locking: All exclusive hold locks must be held until the transactions commit. But if we have transactions that deal with only shared locks, and if the ...
2
votes
1answer
88 views

Can I truncate log in simple recovery mode? [duplicate]

I have a few SQL Server 2005 databases in simple recovery mode. The disk volume in which my transaction logs are stored had only 1GB free, when I started a stored procedure (massive delete in a single ...
0
votes
0answers
27 views

Does RECO always resolve in-doubt transactions ? (Oracle)

I am working on an application which uses a Oracle 11gR2 database in order to fetch and store data. The application is written in C/C++ and some Pro*C, which takes care of everything related to the ...
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 ...
3
votes
2answers
35 views

How to prevent duplicates while storing result from different application

For my case multiple application uses data from db based on date, this data is date specific so if for a date data exist application should just read it otherwise it should fetch information from ...
1
vote
0answers
28 views

PostgreSQL long transaction on a table performance

We have a long processing pipeline running on PostgreSQL 9.0. Is it faster or slower to perform many operations on a single table within one transaction, versus doing auto-commit for each operation? ...
0
votes
0answers
13 views

How do transactions affect PostgreSQL's REFRESH MATERIALIZED VIEW CONCURRENTLY command?

PostgreSQL 9.4 supports REFRESH MATERIALIZED VIEW CONCURRENTLY. I'm not sure, but I think the REFRESH command is viewed as DDL by PostgreSQL, and PostgreSQL wraps all DDL within transactions so that ...
2
votes
1answer
36 views

Selecting only from multiple tables in a transaction

I need to be able to extract rows from multiple tables in a way that I don't get half changed transactions from other users whilst I'm extracting data. The extraction will take some time (over 100 ...
0
votes
1answer
36 views

Generate incrementing IDs without sequences

Suppose there's a multi-tenant application where users can create some kind of documents with basic structure like CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT); CREATE TABLE documents ( ...
0
votes
0answers
16 views

calling multiple procedures from my application Vs calling procedures inside another procedure

I have a scenario of saving user details into my database(MySQL 5.1); i have three tables for saving user details; user_data,user_contact,user_address, so if i insert one user data i have to check for ...
2
votes
1answer
3k views

SQL Server - how transactions and transaction log work (simplified)

I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. Let's consider we have a database with full recovery model. Now, everything ...
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 ...
1
vote
1answer
32 views

mongodb bulk write all or nothing?

I am learning about mongodb. If I create a bulk write is this transaction all or nothing? I have a scenario where my users can delete who they are following. For this to happen I need to delete from ...
1
vote
1answer
32 views

How to view tuples changed in a PostgreSQL transaction?

BEGIN; INSERT INTO foo_table(foo_column) VALUES('a'),('b'),('c'); -- SELECT * FROM pg_stat_activity WHERE ... -- shows records with a flag if they have been added/deleted/updated END; Is ...
0
votes
0answers
19 views

Postgresql Disk Allocation in Begin End Transaction

I have a loop in transaction block that creates a temporary table,updates,exports to txt file and drops table at the end of the block. After some time I noticed that disk size was getting smaller each ...
1
vote
1answer
33 views

Help understanding InnoDB Deadlock Detection

I have a deadlock occurring when executing an Update and a Select in seperate threads but I'm not sure what can be done about it. Previously my understanding was that this could happen in parallel but ...
0
votes
1answer
69 views

Orphaned transaction (SPID -2) with UOW ID 00000000-0000-0000-0000-000000000000

SYSTEM: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor) PROBLEM: Today I encountered an "orphaned ...
0
votes
0answers
178 views

Execute Multiple Queries in a single transaction using Npgsql - C#

I have a Web System using C#.NET + NancyFX and PostgreSQL as my database, and in some functions I have to make insertion in more than one table. For example just to make it clear: I have two tables: ...
0
votes
0answers
34 views

How system resolves when particular resource lock at uncommitted transaction

Let us assume Transaction T1#: Initiated on 2015-10-30 10:25:41.380 Transaction T2#: Initiated on 2015-10-30 10:25:41.580 The above two transactions are initiated through application. T1# & ...
4
votes
2answers
96 views

What are all the ways an open transaction will rollback?

When a transaction is opened in SQL Server, what are all of the ways that it can be rolled back? We are currently working with a 3rd party application that is frequently showing open transactions ...
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 ...
1
vote
0answers
30 views

2 phase locking with added increment mode

I have this assignment question where increment mode is added in addition to read and write lock modes in two phase locking.But I cant seem to figure out whether each lock mode has its own seperate ...
0
votes
0answers
37 views

Creating a database to analyze transaction data for sports events

I would like to create a database to analyze transaction data for sports events. There is already a transactional database from which I can get raw data into excel. I want to transform this raw data ...
0
votes
1answer
92 views

Inserting unrelated data while a transaction is occuring

I'm thinking about implementing transactions to my app to help with consistency issues. I'm using MySQL. I'm wondering what might happen if while inserting several rows as a transaction, the server ...
3
votes
2answers
42 views

innodb_flush_log_at_trx_commit=1 and flush-to-disk reality

I use the default settings of MySQL 5.5 on various servers and I have the following table: create table test._perf ( name varchar(255) not null ) engine=InnoDB; I have an .sql script which ...
0
votes
2answers
77 views

Elements inside a container: How to perform atomic operations on them?

In my DB (Postgres 9.4) I have the following items: Tshirts, Jeans, Shoes and Boxes. Tshirts, Jeans, Shoes can be placed inside a box and they can have a color (the color may be null). Each element ...
6
votes
2answers
172 views

Where does InnoDB store transaction data before committing it?

I've done some tests using READ_COMMITTED and READ_UNCOMMITTED at home, using the JDBC technology. I see that READ_UNCOMMITTED can actually read uncommitted data, e.g. data from some transaction not ...
1
vote
0answers
21 views

Logging within a procedure's transaction

I have an ETL process running inside a PostgreSQL 9.3 procedure on AWS RDS. The procedure normally takes about ten minutes to run, but I'm having a rare issue where it takes several hours and never ...
3
votes
1answer
905 views

Perform reads and writes in the same transaction

I'm trying to implement transactions in my application. I'm just trying to implement it like the example shown in the BeginTransaction() documentation. Public Shared Sub Process(wwid As String, ...