Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
738
questions
1
vote
1
answer
48
views
Why does a SAVE TRAN outside of a CURSOR loop not work, but inside does?
Why, when my SAVE TRAN SavePoint is at location (A) do I get these errors on the third instance of falling into the CATCH and beyond...
"Msg 6401, Level 16, State 1 ... Cannot roll back ...
0
votes
1
answer
20
views
Is commit of first insert necessary if second insert has constraint depedent on first insert?
Using MariaDB I am running an insert on one table and then trying to run another insert on second table. Due to a constraint the second insert should only succeed if the first one was successful.
If I ...
- 103
0
votes
1
answer
60
views
Sql Server Open Transactions
I was notified that the transaction log drive for a sql instance I manage is filling up. I jumped on, found which transaction log was growing, and went to take a look at why the tlog was growing. ...
- 678
0
votes
1
answer
57
views
Rolling back a transaction if a read would return a different value at commit time
I want a certain behavior for transactions in Postgresql, but I can’t tell if it is possible.
I would describe this behavior as an “Optimistic Read Lock”, but that does not seem to be the right term ...
- 3
0
votes
1
answer
29
views
How would you implement Rollback when you need to make sure that 2 updates and 2 inserts either all happen or are all rolled back?
I have a MySQL stored procedure. Cut down, it looks like this.
START TRANSACTION
SELECT some_columns
SET some_variables
UPDATE row_in_balance_table
UPDATE row_in_entry_table
INSERT ...
- 11
0
votes
1
answer
62
views
Keeping Lock across COMMIT AND CHAIN?
I am writing an application that processes jobs. A job does 2 things that are non-transactional: PartA and PartB. Both are idempotent.
A job has three statuses:
Created
PartA_Done
PartB_Done
The ...
- 495
0
votes
0
answers
84
views
Should/can COMMIT/ROLLBACK emit an error when no transaction is active?
What does ISO/IEC 9075-1:2023 standard say about COMMIT/ROLLBACK queries? Should (or can) they emit an error/exception when executed outside an active transaction?
I am asking because of https://jira....
- 398
0
votes
1
answer
45
views
Do PostgreSQL triggers execute in the same transaction as the original statement?
If Table A has a BEFORE INSERT trigger to UPDATE Table B, do those two operations run in the same transaction? Specifically, if the UPDATE on Table B fails, does that mean the INSERT on Table A is ...
- 885
1
vote
1
answer
129
views
Handle partial failures in a transaction (commit selectively) in MSSQL
I am doing a batch update where I process records sequentially (User 1, User 2 etc). Each record has multiple update queries associated to it. I need to skip if there is a data issue for an individual ...
- 849
1
vote
1
answer
106
views
Is there a way to have postgresql transactions be commited in the order they were started?
For some business reason I am running the query SELECT * FROM table WHERE updated_at > $1 AND user_id = $2;.
Consider two transactions, A and B, that are updating rows in table: UPDATE table SET ...
0
votes
0
answers
9
views
Understanding SQL Server Extended Events: Clarification on "count" Column in "database_transaction_begin" Event
I'm currently working with SQL Server Extended Events and have come across an area I'm finding a bit unclear. I'm trying to understand the various event properties, particularly focusing on the "...
- 1,159
0
votes
0
answers
25
views
Mismatch Between Transactions and Sessions in DMVs sys.dm_tran_session_transactions and sys.dm_tran_database_transactions
I've been working with several Dynamic Management Views (DMVs) in SQL Server to monitor transactions, specifically sys.dm_tran_session_transactions, sys.dm_tran_database_transactions, and sys....
- 1,159
0
votes
1
answer
24
views
MYSQL transactions and guaranteeing inserts/deletes/updates
I have a stored procedure called processWinningBet. It takes a pendingBet and does these things.
update balance table for user
creates a row in historic bets table
deletes the corresponding row in ...
- 11
0
votes
1
answer
64
views
PostgreSQL driven by SQLAlchemy - Release Savepoint Idle in Transaction
While running PostgreSQL 13.12 (occurs in several versions of PG11/PG13) using SQLAlchemy 1.3, we are occasionally hitting issues where increased concurrency leaves certain transactions (and their ...
- 20
0
votes
1
answer
30
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 ...
- 315
1
vote
1
answer
147
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, ...
- 123
0
votes
1
answer
41
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 ...
- 295
0
votes
0
answers
70
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
32
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
103
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
59
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
80
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. ...
- 505
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....
- 505
0
votes
1
answer
129
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
67
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 ...
- 206
0
votes
0
answers
28
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
71
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
186
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
165
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
105
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
177
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
522
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
41
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
608
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 ...
- 195
0
votes
0
answers
169
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
698
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
842
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
894
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
53
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
476
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
44
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
130
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
44
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 &...
- 125
0
votes
3
answers
763
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
41
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
358
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
641
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
650
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