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

learn more… | top users | synonyms

4
votes
1answer
454 views

Does a single rollback affect all transactions?

When I run the following script: begin transaction select XACT_STATE() select @@TRANCOUNT begin transaction select XACT_STATE() select @@TRANCOUNT begin transaction ...
1
vote
0answers
18 views

Powershell Invoke-Sqlcmd Implicit Transaction

Does Powershell, through invoke-sqlcmd or through running a query directly through a cmdlet, wrap a query in an implicit transaction? And if so, is there a way to work around this should my query ...
39
votes
3answers
26k views

Is it a bad practice to always create a transaction?

Is it a bad practice to always create a transaction? For example, it is a good practice to create a transaction for nothing but one simple SELECT? What is the cost of creating a transaction when it ...
1
vote
1answer
400 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 ...
1
vote
1answer
337 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 ...
1
vote
0answers
49 views

sys.dm_db_index_physical_stats slow on first run after rebuilding indexes

I have a 10GB database in SQL Server 2016 RC1 on Windows Server 2012 R2 where I just performed many insert and delete operations. Afterwards, I ran: EXEC sp_MSforeachtable @command1="SET ...
1
vote
1answer
143 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 ...
0
votes
1answer
546 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
1answer
33 views

Select after an Uncommited Update in Python-MySQLdb

May be this is a newbie question but want to confirm as this is important for my app: I'm using MySQLdb on Python and my code looks like: db = MySQLdb.connect("localhost","root","",test) cursor = ...
3
votes
2answers
94 views

Transaction in a stored procedure

I need to perform an UPDATE and an INSERT in a single transaction. That code works fine on its own, but I'd like to be able to call it easily and pass in the required parameters. When I try to nest ...
1
vote
2answers
34 views

How do transactions help resilience to system failure?

I understand transactions were developed to meet two requirements: Concurrent DB access Resilience to system failures I understand how the concurrency issue is motivated; to overcome the database ...
6
votes
1answer
141 views

Why is performance non-linear if I do many UPDATEs in a single transaction

An older question covers some reasons why the performance of multiple INSERTS within a single transaction is non-linear as the INSERT count grows. Following some of the advice there, I've been trying ...
2
votes
2answers
101 views

Why would Vertica let me start a transaction if I can't roll it back?

I run the following statements against a Vertica database, one at a time: BEGIN TRANSACTION; UPDATE table SET col1 = 'something' WHERE col2 = 'something else'; SELECT COUNT(*) FROM table WHERE col1 ...
2
votes
1answer
350 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
2k views

Many transactions are getting stuck in the “cleaning up” state - what could cause that?

I'm trying to track down a locking issue, and found via SHOW PROCESSLIST that when the queries in question are waiting for locks and timing out, there are no other queries active in the process list. ...
4
votes
1answer
105 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 ...
4
votes
1answer
117 views

transaction across multiple databases - what is the overhead?

I run the following update, which is one single transaction across 2 databases. Please note I have not committed the transaction. --================================================================ ...
0
votes
2answers
813 views

List of open (uncommitted transactions) in DB2 LUW

How can I get list of open (uncommitted transactions) in DB2 LUW? Or just the oldest transaction id? Basically, I need to get the first change of the oldest open transaction. I found ...
1
vote
1answer
56 views

Long running SELECT query fails as deadlock victim to a DELETE on SQL SERVER - Why, and how to avoid this?

Firstly 1 thread selecting a huge amount of data from a table. Then, before the SELECT query is not yet finished, another thread deletes data from that table. It causes SELECT query to failed with ...
0
votes
0answers
20 views

What is the correct database design for accommodating a request and a receive transactions?

In my project management system, I have a feature that requests a rental from a supplier. Once the set of equipment arrives, I can now create a rental receive to monitor the equipment inventory which ...
1
vote
0answers
50 views

LogFile Size increases by SSIS Package execution

On production server we have 125 GB free space. The SSIS package is doing some delete in Transaction. e.g. Insert 3 columns which are mainly business keys into a staging table and then deleting the ...
1
vote
1answer
75 views

Can I emulate Oracle's autonomous transaction in MySQL?

I have a number of tables that have triggers on them that insert records in queueing and logging tables. The problem is that those inserts are then part of the same transaction. That means that any ...
2
votes
3answers
41 views

MongoDB multi update consistency

I have a collection similar to the below, of say 5,000,000 records. { "_id" : ObjectId("56d18f95d2961d1ddcb7244f"), "rec_id" : 0, "status" : "open", "updated by" : "A" } User B is trying ...
1
vote
0answers
28 views

What's the deal with transactions in a stored procedure called by an SSRS report?

Consider the following: sp_a contains an sp_executesql call to exec sp_b sp_b begins and commits a transaction, then immediately logs @@TRANCOUNT. When I call sp_a manually from within SSMS, the ...
3
votes
1answer
150 views

Howto find out details and reasons for idle transactions?

I have started to use the "Idera SQL check" tool. It shows floating bubbles for each process active on the Sql Server system: Those bubbles are surrounded by a red border in case the transaction is ...
0
votes
1answer
63 views

About transactions on mongodb

To what extent can the atomic storage of MongoDB leave your database unsafe after inserts or updates on my data? In other words: What are the possibilities of my data not been (inserted or updated) ...
0
votes
0answers
48 views

select + update/insert sequence

In my web application (java+mySQL) I have the following server side (pseudo)code: select count(*) c form table where condition; if c>0 then update... else insert... In some situations (heavy ...
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 ...
3
votes
1answer
10k views

Postgres requires commit or rollback after exception

Just trying to understand the thinking here... This bug explains that with other databases, you are allowed to run additional commands after a SQLException occurs. It was written in 2001 but has ...
3
votes
0answers
52 views

Read READ COMMITED transaction advantage in MySQL, high performance object model

I am writing high performance object model with READ COMMITED locking. My question is if there is any reason to starting READ ONLY transaction for read only operations vs. standard SELECT not ...
0
votes
1answer
52 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 ...
2
votes
2answers
135 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
0answers
47 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
91 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 ...
1
vote
1answer
48 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
158 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
231 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
22 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
117 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
57 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
104 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
504 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 ...
4
votes
1answer
62 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
250 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
22 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
90 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
40 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
96 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
42 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
39 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? ...