Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
725
questions
0
votes
0
answers
6
views
Handling data inconsistency of high load time data
I have a highly normalized database PG14 with many foreign keys, and my application performs calculations on this data. First my application loads numerous entities of respective customer (let’s say ...
- 135
0
votes
1
answer
25
views
Create named PK when create table
I'm creating a table in Postgresql-11, and I need create a primary key for it at same time, to prevent from duplicated data being inserted before the PK created, so I want to these two operations be ...
- 285
1
vote
1
answer
44
views
How to handle deadlocks while multiple batch inserts?
I need suggestions/ideas of handling a issue with deadlocks.
I'm having multiple batch inserts commands to same table:
insert into ip (id, p_id, m_type, m_id, sha256, path) VALUES ($1, $2, $3, $4, $5, ...
- 111
0
votes
1
answer
28
views
Can I do transaction for dynamic sql which contains DISABLE change tracking / TRUNCATE / PARTITION SWITCH
I would like to create transaction which will :
DISABLE change tracking IF exists.
TRUNCATE partition for table.
SWITCH PARTITION from different table to main table.
ENABLE change tracking IF was ...
- 283
0
votes
0
answers
17
views
UPDATE AND SELECT INSIDE THE SAME TRANSACTION
I have a transaction that updates a table and then does a SELECT check on that table that was changed, but the data does not follow the update made by the previous UPDATE, there is something I should ...
0
votes
1
answer
28
views
Locking order in a nested query
I'm trying to prevent deadlocks in my code because i may use both in different parts of my project.
Do they lock rows in the same order?
Nested
select * from `carts`
where `carts`.`user_id` = ? and ...
- 57
0
votes
1
answer
58
views
How do two transactions with different levels of isolation interact with each other?
this is specifically for SQLserver:
I want to know if I have a transaction with isolation level SERIALIZABLE and another one with READ UNCOMMITTED how will they interact concurrently?
will the second ...
0
votes
0
answers
53
views
Is it possible deleted partially?
I executed delete query
delete from [table_name]
After execution, I found that 'where clause' is wrong so I cancelled the query using cancel buttion in SSMS.
and then I counted the table record.
I ...
- 1
0
votes
2
answers
63
views
Variable Value Set in TRY CATCH is Discarded When Trigger is Coded a Certain Way
I know this goes against all things logical, however, I am attempting to push this through anyway :(
There is a scenario in which records are written to a table with the trigger below attached. ...
- 463
0
votes
0
answers
43
views
Trigger with EXEC and Only Debug Statements After First Exception Saved [duplicate]
I apologize for the verbosity of this post.
To catch DDL changes after a snapshot or full load (using aws/dms here) has been performed, there is sourceDB.tblAWSObjects which replicates to targetDB....
- 463
0
votes
1
answer
79
views
Transaction/For update lock for an eCommerce project
When users want to purchase their orders, The quantity of the products should be decreased. Now I'm planning to use a Transaction and a FOR UPDATE lock on the products and decrease the quantity of ...
- 57
0
votes
1
answer
64
views
Running 2 inserts in one transaction is executing unacceptably long time. Running same DML Separately works fine
Decided to see what DBAs have to say about this because developers have no idea. I need to execute 2 DMLs in one transaction. First DML takes records from Table1, changes some values and inserts back ...
- 196
0
votes
0
answers
25
views
InnoDB deadlocks with IX locks
Table schema
`id` varchar(255) NOT NULL,
`created_time` bigint DEFAULT NULL,
`updated_time` bigint DEFAULT NULL,
`tdi` varchar(255) NOT NULL,
`gid` varchar(255) NOT NULL,
`auth_id` varchar(...
- 101
0
votes
0
answers
69
views
How are blind writes recoverable in a transaction schedule?
Consider the following schedule -
T1 T2
R(A)
W(A)
R(A)
W(A)
Commit
Commit
I understand that this schedule is non-recoverable, because if a failure occurs between the two ...
1
vote
1
answer
127
views
Lost connection to the database during COMMIT query: did the transaction succeed or not?
PROBLEM
I recently encountered the error 2013 in MySql (Lost connection to MySQL server during query) while executing the COMMIT statement for a transaction (the previous update statements succeeded). ...
- 11
0
votes
0
answers
132
views
multiple mongodb transactions over a single session
I'm using mongodb in an enterprise application and for some use cases, I have to use transactions.
It seems like at first I wasn't using mongodb's ClientSessions correctly. Each time I was trying to ...
- 101
0
votes
0
answers
92
views
How can I simulate transaction ID wraparound in Postgres?
I'm trying to get a better understanding of how XID wraparound affects the results of the query to find the snapshot xmin (that is txid_snapshot_xmin(txid_current_snapshot()) or pg_snapshot_xmin(...
- 1
0
votes
1
answer
142
views
Visibility into progress of Postgres transaction with multiple queries
I have a regular case where I run many Postgres queries within a do/begin/end block
DO $$
BEGIN
<queries>
END $$;
In some cases, the databases and queries run a little bit slow and we need ...
- 3
0
votes
1
answer
2k
views
Getting ERROR: invalid transaction termination CONTEXT: PL/pgSQL function run(text,text,text,double precision) line 147 at COMMIT
I am trying to execute an INSERT statement and, if there's an error then I rollback and retry the insert; otherwise I commit it to close the transaction. However, I am getting the error: "...
- 1
3
votes
2
answers
406
views
In SQL Server Temporal Tables start/end date timestamps are identical for rows that are created inside a EF Core transaction
I'm using a EF Core DbContext transaction to create/update entities on a SQL Server temporal table.
The data I'm processing (array of items) has a key (Year-Sequential, like: 2023-001, 2023-002, etc.) ...
- 151
-1
votes
1
answer
36
views
How to tell Postgresql what tables will be in a transaction?
RDS Postgresql 12.12
CREATE INDEX CONCURRENTLY idx_foo ON foo (f1, f2, f3); is blocked by a stored procedure which only touches tables bar and baz.
BEGIN and START TRANSACTION don't seem to have a ...
- 606
-1
votes
1
answer
257
views
How to avoid deadlock inside transaction? [closed]
I have a transaction that wraps two code paths that update/insert related tables in the opposite order. Outside of a transaction I understand why that would cause a deadlock, but why does it happen ...
- 175
0
votes
0
answers
129
views
Are autonomous transactions implemented in PostgreSQL v15?
I searched the official docs with no luck, but I found a few answers here on DBA referring to them in PG after introducing procedures. Are autonomous transactions implemented in PostgreSQL v15? Thank ...
- 21
3
votes
1
answer
547
views
Will INSERT ON CONFLICT DO NOTHING lock the row in case of conflict?
In read-commited isolation level:
If I understood correctly, in case of no pre-existing rows that would result in conflict, two concurrent transactions with INSERT ... ON CONFLICT DO NOTHING - which ...
- 135
0
votes
2
answers
648
views
Try Catch w/Transaction and executing stored procedures
I need to create an error-handling method for a couple of Stored Procedures.
Proc1
CREATE PROCEDURE dbo.Proc1
AS
BEGIN TRY
BEGIN TRANSACTION
EXEC Proc2
INSERT INTO tableA (col1, col2) ...
- 35
0
votes
1
answer
656
views
MariaDB writing transaction and rollback - novice
Currently I am learning how to write transactions for MariaDB (I got a version 10.6 running).
I attempt to execute several commands and based on their success to commit or rollback.
So far I have ...
- 3
0
votes
0
answers
48
views
Is there a way of identifying transaction boundaries from MongoDB ChangeStream?
The MongoDB documentation (https://www.mongodb.com/docs/manual/reference/change-events/) specifies that for a change event, the transaction associated with that event can be uniquely identified by the ...
0
votes
1
answer
375
views
Logging in error states in Postgres transactions
I'm currently working on rebuilding some SQL Server stored procedures in Postgres, but I have the following hangup
CREATE OR REPLACE PROCEDURE public.math_proc ()
LANGUAGE plpgsql
AS $$
BEGIN
...
- 251
1
vote
1
answer
40
views
MySQL does not open a transaction, and initiates a request containing multiple SQLs.This time, is it a transaction, or is each SQL a transaction?
MySQL does not open a transaction, and initiates a request containing multiple SQLs. This time, is it a transaction, or is each SQL a transaction?
Does not open a transaction means that a transaction ...
- 21
2
votes
2
answers
1k
views
How to interpret "transactions per second"?
Short version: When I execute one transaction in IntelliJ, the "transactions per second" graph increases by 10 tx/sec - I only expect it to increase by 1.
So, it seems that my understanding ...
- 233
1
vote
1
answer
109
views
MySQL does not open a transaction, is there a deadlock between different SQLs?
2 concurrent do not start transactions.
Concurrent A locks 1-3-5 sequentially, of course, the actual locking process must be one by one.Concurrent B locks 5-8-1 sequentially, of course, the actual ...
- 21
0
votes
0
answers
30
views
Postgres, how to select and update in transaction via jdbc?
I'm trying to implement simple logic, there is a table with jobs my_jobs
jobId | jobStatus | jobMeta | executorId
1 ACCEPTED "a stuff to do" | null
2 ACCEPTED &...
- 123
0
votes
3
answers
591
views
How do I handle an INSERT to 2 tables within a transaction?
I have 3 tables in a Postgres DB:
UserType -> ID, creation, delete, update, name
User -> ID, creation, deleted, update, user_type_id
Employee -> ID, creation, deleted, update, user_id (fk)
...
- 1
0
votes
1
answer
37
views
Problem associated with transaction schedule
Here, Read_item(B); and B;=B+temp; have written parallelly. what happen here? What is the problem associated with the above transaction schedule?
0
votes
1
answer
315
views
Phantom read during single statement execution in PostgreSQL
I wonder could there ever be a concurrent situation when phantom read occurs during single SQL statement execution?
Given: tx_test is a table with 3 rows and id column is primary key, value is some ...
- 188
0
votes
1
answer
508
views
Using two transactions at the same time without getting Lock wait timeout exceeded
I use MySQL 5.6
I'm trying to make a test framework on PHPunit, I use two different db connections (from two different systems it has to be 2 different db connections not a choice). By default many ...
- 103
1
vote
1
answer
588
views
Does MS SQL Server hold transactions in queue?
Is it possible for SQL Server to 'hold' transactions and then execute them in one go?
Just some background:
I have a client with a decently large SQL Server with their ERP databases and our scanning ...
- 11
0
votes
2
answers
945
views
Guaranteed rollback in SSIS
I'm trying to create a job in SSIS to truncate a table, read from a data source and rewrite the table, so only 2 steps (1 Truncate and 2 Select + Insert). I need to guarantee a Truncate's Rollback in ...
1
vote
2
answers
2k
views
What happens to an open transaction when the session window is closed?
I executed the following query in SQL Server 2012:
create table testTransaction
(
id int
name varchar(100)
)
insert into testTransaction values (1,'ABC'),(2,'XYZ')
/* Query1 */
begin try
begin ...
- 435
0
votes
1
answer
403
views
Aggregate each row and create new monthly column for the weeks to a month
I appreciate all your help. Below is my query and request.
Here is my staging table:
CREATE TABLE MyTable (
[WC] nvarchar(255),
[Requestor] nvarchar(255),
[MTR#] nvarchar (255),
[Date Added] ...
- 3
0
votes
2
answers
594
views
how long should an sql-server transaction run at most [closed]
our Teamcenter (Siemens' software) oftens runs transactions for hours, even days. As if they were forgotten.
There easily are even 20+ transactions running longer than an hour, even 20 hours.
They ...
- 127
0
votes
2
answers
98
views
Holding transactions as long as a user edits
As an application developer I'm used to using databases transactions only as a way to play in modifications after a user has clicked "save".
That's the way most database servers I'm familiar ...
- 755
3
votes
1
answer
279
views
The finer points of writers locking each other out under the snapshot isolation levels
The read committed snapshot and snapshot isolation levels in SQL Server do away with most locking except one: A writer still locks out other writers.
The documentation tiptoes around saying as much ...
- 755
0
votes
1
answer
2k
views
Run both of "DROP DATABASE" and "CREAT DATABASE" in CLI with "-c" option of psql of Postgresql
When debugging programs, I need to frequently drop a database of Postgresql, and then re-create it again.
To convenient, I want to execute this in a single command line like following:
psql -c "...
- 285
0
votes
1
answer
650
views
pg_restore error when using the --single-transaction: options c/--clean and -a/--data-only cannot be used together (even so the -a flag was not used)
I met a strange PostgreSQL (v.13.8) on a Windows server 2019 machine today when trying to restore a custom dump file.
The following command worked:
pg_restore -d postgres://postgres@localhost:5432/...
- 342
0
votes
1
answer
202
views
PostgreSQL: does the WAL group commit affect synchronous replication performance?
I'm curious if PostgreSQL's Group Commit mechanism (controlled by commit_delay and commit_siblings parameters) only affects disk fsync calls.
Or does it also allow multiple on-the-fly transactions to ...
- 103
0
votes
0
answers
116
views
Behavior Transactions PDO::beginTransaction() and "BEGIN"
I have a strange behavior in a php script (PHP 8.1) using MariaDB 10.7.5 database.
This one with native SQL works:
$pdo->query("BEGIN");
[ ... some database stuff ... ]
$pdo->query(&...
- 97
0
votes
1
answer
135
views
Debug application performing transaction
I have an application to debug which performs a few dozen operations against a MySQL database. These operations are performed in a single transaction. I need to know the state of the database while ...
- 1,075
0
votes
0
answers
61
views
Locking for secondary B-tree
I implemented a heap with MVCC; that is, each tuple in the heap has a history list so that updating the tuple does not block reading. I also implemented a secondary B+ tree; that is, it stores (...
- 15
0
votes
2
answers
698
views
Concurrently update counter with least amount of locking
Using Sql Server I need to have concurrent transactions update a counter, when they commit - but they must not block each other from updating the counter.
Let's say the first started transaction takes ...
- 225