Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
3
votes
1answer
71 views
An uncommitted transaction updates a column; how can a different transaction see the old column value?
Let's assume the following case.
There is a students table:
id | name
-----------------
1 | Mateus
A "User A" executes the following statement without committing the transaction:
update students ...
8
votes
3answers
844 views
transaction log in RAM or physical file?
I'm a beginner in transaction, just a question on transaction log.
We know that when we commit a transaction, the changes are written to the transaction log, but is transaction log in RAM or physical ...
9
votes
3answers
481 views
Does SQL Server allow (make visible) DDL inside a transaction to the transaction prior to commit?
In PostgreSQL I can create a table with some test data, and then in a transaction migrate it to a new column of a different type resulting in one table-rewrite upon COMMIT,
CREATE TABLE foo ( a int );...
3
votes
2answers
82 views
Gap Locking in Read Committed isolation level in Mysql
MYSQL VERSION : 5.7.X
STORAGE ENGINE : Innodb
I have a general idea that Read Committed Isolation will mostly use Shared and Exclusive Record Locks. But, as per mysql docs, there are some cases where ...
1
vote
0answers
19 views
Preserving some data on rollback in InnoDB?
I want to keep log of attempted, sucessful, and failed operations (both for troubleshooting, and also for detecting malicious users trying things). I want to keep that log in InnoDB MySQL table.
Now, ...
4
votes
1answer
29 views
postgres notifications from inside stored procedures/triggers not immediately received by node.js listener client
We are using postgres 11's stored procedures to do looped update of rows in batches. After a batch is updated, a 'progress' table is updated with progress percent and status, THEN a notification (...
4
votes
1answer
66 views
How to avoid deadlock for this query?
I am running a MySql 5.7 database and I have a query which results in a deadlock as it is running in multiple threads.
I am trying to figure out how to avoid this deadlock, but so far I haven't been ...
1
vote
2answers
143 views
Very slow simple INSERT in transaction
I have I problematic query on my SQL Server where I could not find the problem. I have two simple tables, one for the "Headers" and one for the "Items" (each with an PK column, total of five fields ...
3
votes
2answers
93 views
Best practices for committing a transaction in SQL Server where TRY CATCH is used
In a SQL Server code block, what is the best place to place the commit transaction? Inside the try catch block or outside it?.
For example, is option A or option B the correct approach or are they ...
1
vote
1answer
41 views
PostgreSQL ensure no concurrent reads
I'm sorry if it's a very basic question, but would really appreciate educated advice on this:
Say I have an operation, which first has to read from a table and then insert a row if the read value ...
0
votes
1answer
37 views
Safe to use the same temporary table name in parallel transactons?
Using postgreSQL 10 or 11, would it be safe creating temporary tables with the same name, each carrying different data? I do not intend to share the data between any sessions/connection/transactions. ...
0
votes
0answers
39 views
Transaction on defriend and multi databases
I have a big issue when i'm working with multi database and diffrent databases as same time.
The problem is that I'm working with mssql, mysql and mongodb at sametime, and what I need is a way to be ...
1
vote
1answer
14 views
Wraparound Transaction ID
Once the txid has passed the the xmin/xmax 32 bit limit, and assuming you've not suffered any issues with wraparound because you've been correctly maintaining your tables (plenty of vacuums, vacuum ...
2
votes
1answer
90 views
Is it possible to step through TSQL that uses variables, in SSMS?
If I enter TSQL into SSMS Query editor, I know I can "step through" the statements (ie. execute them one at a time) by highlighting a statement and hitting F5 on the keyboard.
It seems by default ...
-1
votes
2answers
46 views
Is there any possible solution to insert correct records and prevent incorrect records from being inserted by insert statement in SQL server? [closed]
I know that each statement (i.e: Insert , Update , Delete ) in SQL-Server is treated as one single transaction and I know that one of the main characteristics of a transaction is it's 'Atomicity' so ...
5
votes
3answers
547 views
How to inspect global temp table from outside the transaction it is created in
To help debugging a batch of SQL (that is run inside a transaction), inside the transaction I dump some data into a global temporary table. The global temporary table is created inside the transaction ...
0
votes
0answers
15 views
InnoDB: does `redo log` contain initial value and final value? or just final value?
start transaction;
update users set age = 34 where user_id = 3 // update age from 30 to 34 years old.
....
....
when I am updating the age of this user, InnDB will write redo log.
What's the ...
0
votes
0answers
30 views
MySQL: why undo log is required in the processes of database recovery?
Let's imagine that an incident happens when you updates.
start transaction;
update users set gender = 1 where id = 3;
Undo log space is allocated.
Previous values are copied to undo log
Record of ...
1
vote
2answers
38 views
How a Transaction is atomic?
I am really confused as to how to digest the fact that a transaction is atomic. If a transaction is set of "queries" then how will it be atomic. I am relating the word "queries" to a general SQL query....
0
votes
0answers
20 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
30 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
58 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
25 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
21 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
119 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 ...
0
votes
1answer
183 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
55 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
57 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
419 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 ...
3
votes
2answers
74 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
32 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
36 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
40 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
94 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
62 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();
-...
7
votes
2answers
140 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
65 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
418 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. -...
1
vote
1answer
125 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
26 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
162 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
76 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
44 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?
...
1
vote
2answers
86 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
38 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
61 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
44 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
1answer
63 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 ...
7
votes
3answers
2k 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
1k 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 ...
