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

learn more… | top users | synonyms

4
votes
1answer
89 views
+50

MySQL locking in Duplicate Key Error

From the docs: If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to ...
7
votes
3answers
949 views

What is the effect of having an opened transaction in MSSQL for too long?

I'm just wondering what happens if you begin a transaction in a DB and forgot to commit or rollback it. Will the server be down? Lets say you left it for 3 days. There are also users who are using it ...
4
votes
3answers
78 views

Behavior of transactions on multiple connections

I have a MariaDB database with InnoDB tables. I have two connections to the same database. One connection is using a transaction around a large chunk of work. The other connection is tasked with ...
1
vote
1answer
498 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 update)/...
1
vote
1answer
160 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 ...
1
vote
1answer
425 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 ...
0
votes
1answer
605 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 ...
2
votes
1answer
318 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 ...
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. ...
2
votes
1answer
423 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 ...
0
votes
1answer
40 views

Transaction on delete is committed, but for a brief time the entries can still be read?

I have a Java application that uses Spring JDBC and HikariCP for pooling MySQL connections. There is a high level of concurrency where a thread creates a unique entry for a given task in a single ...
0
votes
0answers
19 views

postgresql transactions - phisically where records are stored in the time frame between BEGIN and COMMIT?

I'm working on a script which perform a huge transaction, before commit around 25 GB of data is processed. My Question is, where are records physically stored in the transaction time laps between ...
3
votes
1answer
67 views

Diagnosing a block in a process that EXECs several layers of stored procedures

Disclaimer: I am a developer. Please be nice to me. I am not the developer that is responsible for what follows. I try to be one of the good ones. I have inherited a support ticket that involves a ...
47
votes
4answers
36k views

What risks are there if we enable read committed snapshot in sql-server?

I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there? eg. Will this affect recovery of the database? Is there anything ...
1
vote
1answer
30 views

Restoring a mysql database using --single-transaction

I learned that specifying "--single-transaction" (yes we do have InnoDB) option into the mysqldump tool produces a dump utilizing transactions. However, I am failing to find a way to restore the ...
1
vote
1answer
22 views

How to run a job in snapshot isolation?

We have a long running job for report data generation and we are not worried about changes to the source data after the job has started. For this case the SNAPSHOT isolation mode seems to be a good ...
2
votes
1answer
125 views

SQL Server 2008 R2 Transaction Counts

Good Afternoon, We have a pretty big SQL Box 32proc 128gb. We had a change in our environment, not sure what exactly change, db/code. We're investigating that aspect as well. One of the things ...
5
votes
3answers
3k views

Transactions within a Transaction

What behaviour would PostgreSQL display if for example the script below were called BEGIN; SELECT * FROM foo; INSERT INTO foo(name) VALUES ('bar'); BEGIN; <- The point of interest END; Would ...
4
votes
1answer
86 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 ...
1
vote
0answers
30 views

MySQL/JDBC: Can a commit on server get missed on client?

I am wondering about when conducting a transaction of one or two updates/inserts from a JDBC client to MySQL Server over the network... In the event of losing the network during the commit, can I get ...
3
votes
2answers
75 views

Rules related to Transaction count

I was reading the examples on MSDN for TRY-CATCH blocks. The code listing for example B is as follows: BEGIN TRANSACTION; BEGIN TRY -- Generate a constraint violation error. DELETE FROM ...
3
votes
2answers
151 views

Can we commit the transaction when the sql server trigger fails?

I am trying to avoid data loss when the insert trigger on a table fails. I am trying this with the following scenario and my code is failing. When an insert happens on the Customers table, I want to ...
5
votes
1answer
524 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
49 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 ...
46
votes
3answers
29k 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
0answers
58 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 ...
0
votes
1answer
49 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 = db....
3
votes
2answers
110 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
38 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
151 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
166 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 ...
4
votes
1answer
184 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
134 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
961 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
85 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
22 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
141 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
52 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
35 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
156 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
65 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
51 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
11k 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
58 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
54 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
145 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
50 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
139 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 ...