Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
0
votes
0answers
9 views
Utilizing DBLINK[_EXEC] to control transactions within PL/PgSQL functions?
I am exploring options to control transactions within functions, mainly to reduce the memory footprint for large operations over multiple individual units. I.e. large data-sets are inserted regularly, ...
0
votes
0answers
24 views
Why Update join sql locks a table in Mysql 5.6
I'm encountering transaction error like this.
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-12-25 08:45:10 2b128be88700
*** (1) TRANSACTION:
TRANSACTION 112497037, ...
0
votes
1answer
39 views
Using `transactions` without `select … for update`?
I have a table of ranges that should never intersect:
id | from | to
--------------
1 | 3 | 5
2 | 12 | 14
3 | 20 | 24
4 | 27 | 30
I have an algorithm that does the following:
Find gaps ...
0
votes
2answers
20 views
Keep a history of transactions for easy rollback
Is there a database (either SQL or NoSQL) which allows you to keep a history or log of all past transactions so that you can easily rollback to any given point in time (similar to how git manages a ...
1
vote
1answer
19 views
Database transaction consistency on PostgreSQL
In a table with three columns (serial id, varchar name, varchar email) and having two different transactions executing concurrently as the ones bellow:
Transaction 1:
BEGIN;
INSERT INTO public.users ...
1
vote
2answers
45 views
Elevate transaction isolation in Maria DB
I am migrating code from Microsoft SQL to Maria DB. In the MSSQL stored procedure a transaction will be started with repeatable read and then later conditionally elevated to serializable if needed. In ...
-1
votes
1answer
39 views
SSIS Error :Cannot roll back .No transaction or savepoint of that name was found
Hope you are Doing well.
I have 1 SSIS Package as you can see below :
In the "Insert" part of the Package which is an Execute sql task,
I'm executing a Stored procedure . this is the code inside the ...
1
vote
1answer
49 views
Commit() takes 50 seconds after inserting about 100000 rows
I have a sqlite database that is, (I think), fairly small.
I has about 1 million rows in a couple of table and the total space on disk is about 150 Mb.
I then open a transaction and insert about ...
2
votes
1answer
54 views
Which database transaction log will increase?
I found a query miss written in one of our databases like this
Use database2
go
delete database1.dbo.table1
from table1 as t1
join table2 on t1.column1 = t2.column2 and t2.column3 = 1
I am having a ...
1
vote
1answer
45 views
How to use variables in procedure (not function)? [closed]
As I understand, in Postgres, procedures are for manipulating data and functions are for reading data. I would like to:
declare a variable
select a value into a variable
insert the variable into ...
2
votes
2answers
68 views
Can independent applications cause roll back to each other's transactions?
I have a database built on SQL Server
Application-1 [Legacy App]:
Written in C#, and runs transactions on the above database.
Application-2 [New application being developed]:
Written in Python, ...
0
votes
1answer
25 views
BEGIN / COMMIT atomic update
I try to generate unique identifiers for the ticket table's records.
A ticket belongs to a file record, which also have an identifier.
If the file's identifier is 'SOMEPREFIX/F01' then its tickets ...
1
vote
2answers
32 views
Update same field from multiple transaction
I have a service which makes some manipulations in DB, everything is working normally until I tried to make two requests in parallel.
Below see the sql debug log:
Executing (2c4cb493-7090-4a83-942a-...
1
vote
1answer
37 views
Things affected globally in a postgresql transaction
Correct me if I am using the correct term: "affected globally" I mean, if I increase the sequence value, it will be increased for all other running transactions without the need for commit....
I ...
1
vote
1answer
88 views
Should SQL Server 2000 throw an error if update statement fails within a SP?
I'm troubleshooting an issue on SQL Server 2000 where it appears that a update statement in a stored procedure failed, and the check for @@ERROR <> 0 didn't catch it, so the transaction was ...
2
votes
1answer
45 views
Select rows changed in current transaction
We have table name and CURRENT_TRANSACTION_ID().
How can we SELECT all rows INSERTed/UPDATEd in current transaction in MS SQL Server?
For example,
BEGIN TRANSACTION;
EXEC some_big_procedure();
-...
6
votes
2answers
104 views
Postgres Transaction OOM on 100k DDL statements
We execute approximately 100k DDL statements in a single transaction in PostgreSQL. During execution, the respective Postgres connection gradually increases on its memory usage and once it can't ...
3
votes
0answers
38 views
Why do two transactions making a read lock result in a DEADLOCK exception?
Let's assume a scenario that flows as follows:
Transaction #1 makes this query:
SELECT v.id, v.name
FROM versions_history as vh
INNER JOIN versions as v
ON vh.version_id = v.id
WHERE vh.project_id = ...
2
votes
1answer
231 views
Connection pool Error
I am getting the below error from my .net application.
Source: xxx.Services.xxx.xxx.xxxx.xxxx.xxxxRequest
---
System.Data.Entity.Core.EntityException: The underlying provider failed on Open. -...
0
votes
0answers
27 views
how can snapshot isolation automatically prevent lost updates?
I'm reading the book Designing Data-Intensive Applications and there's a part of it that I don't really understand (below). The concept of "lost updates" that it's referring to is when two ...
0
votes
0answers
11 views
preventing some resource described in a database from being acquired more than once
I'm assuming SQL databases for this question. It's based on the diagram at the bottom from the Designing Data-Intensive Applications book.
The diagram describes a dirty write scenario that cannot ...
1
vote
1answer
42 views
advantages of single-leader (transactions) over multi-leader replication
I am reading the excellent book "Designing Data-Intensive Applications" which I wholeheartedly recommend, but I'm confused by a section comparing multi-leader (i.e. multi-writer) replication to single-...
0
votes
1answer
25 views
What is the best solution to prevent picking the same value (by multiple requests)?
Situation: there is a table "Tours" which contains a lot of generated records with different properties (Color, Priority, PickedUp). The goal is to allow external application (over REST API) to pick ...
1
vote
1answer
81 views
What will happen with MySQL transaction if one of the queries fails?
I have 500 independent insert queries. I put them in a transaction to run faster. I am runing queries with PHP.
What will happen if one of queries fails? Will the transaction be rolled back ...
1
vote
1answer
58 views
Queries logged in the 'Slow query log', and 'start transaction' vs 'set autocommit = 0'
Currently long_query_time is set to 5, and that works.
However, many queries are part of a transaction, and the slow query log only keeps the final commit;.
# Time: 2018-08-15T15:18:18.205309Z
# ...
2
votes
2answers
37 views
What happens if the database node's network fails just after commit and before responding to the client
I know the transaction will be rolled back if connection breaks before commiting.
But what if the commit operation reached the sql server and before it could respond to the client, the network breaks?
...
0
votes
1answer
50 views
Select data from a table while update is running within a transaction in SQL Server
The following sql query is used to update a specific row inside transaction.
BEGIN TRANSACTION;
Update [dbo].[StudentExamAttendanceDetails] SET StudentFeedBack = 1 Where Id = 4883174
COMMIT ...
1
vote
2answers
56 views
Why do uncommitted transactions rollback instead of redo after a crash in ARIES?
Since all actions within a transaction are idempotent in crash recovery algorithms like ARIES that use write-ahead logging why do we rollback after a crash during an uncommitted transaction to get ...
1
vote
1answer
36 views
Update can't see records just committed by another connection
I am performing a transaction using a connection. Whenever a second connection immediately tries to update the rows just inserted, the rows are not found. Even if the transaction was committed before ...
0
votes
0answers
59 views
Oracle - Is it possible for the results of a transaction not to be visible to a subsequent transaction?
We're having a strange problem with a flaky integration test. This is using Java/JDBC against an Oracle 12 database. The test does this, in pseudocode:
transaction 1 {
insert into order (id, ...
0
votes
0answers
38 views
Proper Use of Transactions
I am learning more about SQL as I work on this Database project. Today I encountered Transactions, and after looking at what they are and how they work, my first thought was, "Shit, I need to change ...
0
votes
0answers
18 views
SQL - Error when commiting - Mismatch begin and commit
I'm receiving the following error when running this code. "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1."
My ...
0
votes
1answer
60 views
Data locking question
I'm learning locking basics and I have some doubts about how it works.
I'm using following query to examine transaction locks :
SELECT request_session_id AS spid ,
DB_NAME(resource_database_id) AS ...
5
votes
3answers
655 views
Concurrent transactions result in race condition with unique constraint on insert
I have a web service (http api) which allows a user to restfully create a resource. After authentication and validation I pass off the data to a Postgres function and allow it to check authorisation ...
-2
votes
3answers
377 views
How to handle ~1k inserts per second [closed]
Assuming one has about 1k requests per second that require an insert.
Now, there are a lot of answers to this on the internet... but they're technically wrong in this specific context. Yes, pretty ...
1
vote
1answer
163 views
Handling 'Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements'
Our systems suddenly started failing on:
Transaction count after EXECUTE indicates a mismatching number of
BEGIN and COMMIT statements.
There was no service pack or other installation changes ...
0
votes
1answer
111 views
How to add slots for Slot table such that there are no clash
I have a Slot Table which has following fields :
CREATE TABLE `Slot` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`starttime` datetime NOT NULL,
`endtime` datetime NOT NULL,
...
2
votes
3answers
498 views
Why does this query result in deadlock?
I provide below the raw MySQL query and also the code in which I do that programatically. If two requests are being done at the same time results in the following error pattern:
SQLSTATE[40001]: ...
1
vote
1answer
25 views
Can sub-selects change in one single query in a read committed transaction?
I am querying the same table multiple times in a single query in a transaction with isolation level set to READ COMMITTED (which is the default in PostgreSQL). Examples are:
BEGIN;
WITH
first AS (...
0
votes
1answer
35 views
Does READ COMMITED commited blocks any rows?
I know that there are four diffrent types of isolation levels in databases. I've read many articles about that but still, I don't get the idea of Read Commited level. Does this level lock the rows ...
0
votes
0answers
78 views
measuring transaction processing time in mssql
Is there a way to measure the (avg) time it takes mssql to process a transaction across a set of transactions (or spanning a certain period)? I'm thinking potentially of sys.dm_os_performance_counters)...
0
votes
1answer
25 views
Mysql - Rows not updated in transactions without any errors
I have a stored procedure that updates a user's balance and inserts a transaction log. I don't know why, but sometimes it doesn't update the users table.
Stored Procedure Code
DELIMITER $$
USE `...
1
vote
1answer
86 views
Transaction log growing out of control on random times [duplicate]
We have a problem with one of our databases. At random times something causes our transaction log to swell up to 20x its original size (it basically fills up the whole HD, so 20x is not accurate since ...
2
votes
1answer
61 views
How to check if the current connection is in a transaction?
I need my program code to ensure that certain part of logic is being executed within a transaction.
What query would tell me the current transaction ID / other information that would allow me to ...
0
votes
0answers
305 views
Create temporary tables in read only transaction - MYSQL 5.7
I am currently assisting with integration testing for migrating an application that currently runs against a mysql 5.5 DB to mysql 5.7. We have run into issues related to functionality that was ...
0
votes
2answers
104 views
Will Postgres Deadlock on Single Update
If the two updates given in https://www.postgresql.org/docs/current/static/explicit-locking.html#LOCKING-DEADLOCKS happen in one UPDATE query will it still result in deadlock?
Transaction 1 executes ...
1
vote
1answer
170 views
Difference between a trigger and a transaction [closed]
I'm implementing movie rating service, so I'm considering about using triggers or transactions.
What I'm considering is inserting movie information into a movie_list and to insert the genre of that ...
2
votes
1answer
105 views
What happens with WITH if later the query fails
I am by no means a database expert so I might be asking something dumb.
So I am using PostgreSQL and I want to do the insert to one table, keep the ID (ID is incrementing automatically) and insert a ...
2
votes
2answers
300 views
Postgresql large transaction OOM killer
We have a Postgresql 9.5 running on CentOS release 6.5 (Final)
The server has 16GB of ram.
We are running a long transaction which adds and updates 10s of millions of rows in multiple tables. Besides ...
4
votes
1answer
622 views
Setting READ_COMMITTED_SNAPSHOT to ON while ALLOW_SNAPSHOT_ISOLATION is OFF
I experimented with various configurations of SQL Server databases and ended up setting READ_COMMITTED_SNAPSHOT to ON while ALLOW_SNAPSHOT_ISOLATION is OFF.
I noticed that when enabling this, many ...
