Questions tagged [transaction]
A mechanism for committing a consistent set of changes into a database atomically.
764
questions
0
votes
0
answers
44
views
SQL via JDBC fails on MSSQL Server with BEGIN TRAN
Running this SQL via JDBC on MSSQL works as expected:
UPDATE costcenter
SET updatetimestamp = Getdate(),
[de] = 'CostCenterName',
[en] = 'CostCenterName_en'
WHERE [customerid] = '...
2
votes
2
answers
271
views
If a trigger runs an update will it ALWAYS have the same timestamp for a temporal table?
Background
Here is an example near to what I am working with:
CREATE TABLE sandboxTesting.TemporalTest (
GroupNumber VARCHAR(25) NOT NULL,
StartEffectiveWhen DATE NOT NULL,
...
- 2,540
0
votes
0
answers
14
views
Understanding MySQL's Deadlock for un-related tables
I see the following Deadlock in my show engine innodb status's output:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-08-22 09:35:46 0x7f70f2d2d700
*** (1) TRANSACTION:...
- 21
0
votes
0
answers
40
views
PostgreSQL: using stale statistics instead of current ones because stats collector is not responding
We are running PostgreSQL 13 on Azure Centos VM and found this problem from the logs followed by some slow commit and SET statements. The slow statement logs are only lasted for a period of less than ...
- 341
1
vote
1
answer
47
views
Setting a value for max_locks_per_transaction
We find ourselves in a situation where we need to increase the value max_locks_per_transaction as the default isn't appropriate. I've spent some time looking and I can't find any information to ...
- 23
0
votes
2
answers
112
views
Why must I commit after the ALTER TABLE DDL to make changes visible?
If I execute this in DBeaver:
alter table classes alter column reference set not null;
on my local database which only I have open in DBeaver, it completes successfully.
But then I can't open the ...
- 203
0
votes
0
answers
21
views
why the mysql 8 database auto generate a long running transaction
Today when I run an update command run spring boot application, the MySQL 8 generate a long running transaction and block the next update command. This is the output when I check the transaction:
...
- 815
0
votes
1
answer
46
views
Conditional update
I would like to create a conditional update on an audit table, such that if the update on the main table succeeds, then the update on the audit table is performed.
Right now I have this
CREATE DEFINER=...
- 1
0
votes
0
answers
9
views
Serialisable Schedule but not View Serialisable
These are the definitions I have for Serialisable and View Serialisable.
I know that View Serialisability => Serialisability and I want to find an example of a schedule that is Serialisable but ...
- 1
2
votes
2
answers
1k
views
Why does the SERIALIZABLE transaction isolation level not guarantee point-in-time views of data?
I have been told that if your transaction consists of multiple statements, then the SNAPSHOT transaction isolation level is the only way to guarantee consistent point-in-time views of data. Why is the ...
- 645
0
votes
1
answer
75
views
Optimizing XID management in PostgreSQL: How to avoid INSERT locks in tables?
I would like to raise a question regarding the optimal practices for managing transaction age (XID) in a PostgreSQL database.
I am currently using the method of performing VACUUM FULL operations on ...
- 1
1
vote
1
answer
98
views
Limiting the number of rows that can be inserted for a given WHERE clause
In Postgres, I want to be able to confirm users' RSVPs for an event that has limited capacity. How do I do this in a way that protects against race conditions?
I have a table event_attendance with ...
- 141
0
votes
0
answers
110
views
Handling deadlocks in Laravel during concurrent DELETE and INSERT , ON DUPLICATE KEY UPDATE operations
in Laravel I have a service which is not wrapped in a transaction and involves both INSERT ... ON DUPLICATE KEY UPDATE and DELETE statements targeting the same table, customers.
INSERT INTO customers (...
- 1
2
votes
0
answers
136
views
'Deadlock found when trying to get lock; try restarting transaction' in AWS RDS MySQL 8.0.36 after upgrading from MySQL 5.7.42. Django v4.1.6
Following an upgrade of our AWS RDS instance from MySQL version 5.7.42 to version 8.0.36, we are encountering persistent deadlock issues across concurrent transactions, affecting even basic read ...
- 21
0
votes
0
answers
21
views
I need help pulling info from a .txt file into a table in either access or excel
So.. I've tried both Excel and Access to do what I need but I don't know the programs well enough to get them to do what I want, if it's even possible.
I have a hourly updating .txt file with ...
- 101
1
vote
1
answer
113
views
What happens when RCSI is enabled in one database but not another, and you query both?
Suppose that I have two databases on the same server, RCSI_Yes and RSCI_No. In the first, RCSI is enabled. In the second, it is not. How does SQL Server decide what transaction isolation level should ...
- 645
1
vote
2
answers
67
views
MySQL exclusive lock not appearing to work for new child rows
I have some logic in my application which I think results in the following MySQL calls, however when I get two of these run within a couple of milliseconds, I get two incompatible child rows.
Start ...
- 125
-4
votes
2
answers
276
views
What do developers need to know about Read Committed Snapshot Isolation? [closed]
My developers have only ever worked with Read Committed, but my new server has Read Committed Snapshot Isolation (RCSI) enabled. What do they need to do differently when writing application code?
- 645
2
votes
1
answer
465
views
Catching exceptions and rolling back transactions
I am just trying to see if I understand this correctly:
By default, any error occurring in a PL/pgSQL function aborts
execution of the function and the surrounding transaction
I have a procedure ...
- 35
1
vote
1
answer
78
views
Is MariaDB Acquiring Exclusive Lock during Delete?
There is a Spring batch job with one delete and many inserts within a transaction with isolation level READ_COMMITTED:
delete from TABLE_A;
insert into TABLE_A values (...);
insert into TABLE_A values ...
- 15
1
vote
1
answer
83
views
how can I see how many records have been affected by a transaction before it is committed? or how costly would be the rollback if I have to kill it?
I am using sqlwatch to monitor some of my servers and it has a job that deletes in batch from a table called dbo.sqlwatch_logger_snapshot_header
I had lots of problems of locking and blocking as this ...
- 16.4k
0
votes
1
answer
73
views
Repeatable reads in case protection from duplicated inserts in POSTGRES
I would need to perform this kind of logic in a serializable way:
account table has columns: 'accountName', 'location', 'worth', ...
Assume no unique key exists.
Some accounts are logically (according ...
- 113
0
votes
1
answer
132
views
Understanding Lock Behavior in SQL Server Deadlock Scenario with INSERT
Hello SQL Server Experts,
I'm currently experimenting with SQL Server deadlocks and have intentionally created one to better understand locking behavior. After setting up a test table, I ran two ...
- 2,042
1
vote
2
answers
99
views
Transaction management work under Instance or database
I remember from university that:
By Default, doing Insert, Delete and update on 2 or more data sources can't be participant under a transaction
Please consider this this code:
begin tran Insert2
...
- 203
0
votes
1
answer
76
views
How to set up locks to prevent stale data being written
I'm mainly a C#/Javascript developer, with only a basic understanding of SQL and databases. I've been doing some reading on transactions and locks, but I'm still struggling to figure out how to set ...
- 1
0
votes
0
answers
57
views
Why postgresql creates force subtransactions in large statement except sql commands (savepoint...)?
I performed following large transaction without any sub transactions:
begin
for i in 1..10000000 loop
INSERT INTO src.customers
(id, first_name, last_name, email)
values
(i,'First Name', 'Last Name', '...
3
votes
1
answer
378
views
SQL Server do not block table/row when I update row to the same value
create table test (
id int identity,
id_int int default 1
)
insert test default values
go 1000
begin transaction
update test
set id_int = id_int
where id = 1000
waitfor delay '00:00:10'
...
-1
votes
1
answer
247
views
Single-statement transactional consistency
I ran into this Q&A on StackOverflow the other day. To quote comments from the question author, the situation seems "somewhat horrifying". A similar situation is set up in this Q&A.
...
- 607
0
votes
1
answer
91
views
When can we say a transaction is committed in mysql?
I know the transaction can ensure that all the queries are committed or rolled back, but what is the point at which we can say ok, the transaction has been committed and all changes are durable?
I ...
- 13
0
votes
1
answer
144
views
How to atomically update different databases?
I have a lambda function which receives data of newly signed up users from a message queue and insert that same data in two different databases.
Those two databases belong to two different ...
- 103
1
vote
1
answer
72
views
Does a commit occur all at same time from a transactional point of view?
Assume a "Bank_Account" table exists and contains a single column "balance", where everyone has balance=100.
If a transaction performs "update Bank_Account set balance = 200 ...
- 113
5
votes
1
answer
1k
views
how to kill a rogue spid that is already killed?
I have an obstinate spid that I cannot kill and it is preventing the transaction log of my tempdb to get truncated
this is how I found this rogue spid:
if object_id('tempdb..#OpenTranStatus','U') is ...
- 16.4k
1
vote
1
answer
115
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
42
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
160
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. ...
- 688
0
votes
1
answer
153
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
80
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
118
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 ...
- 539
0
votes
0
answers
111
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....
- 418
2
votes
1
answer
813
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 ...
- 925
1
vote
1
answer
242
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 ...
- 889
1
vote
1
answer
731
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
14
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,199
0
votes
0
answers
52
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,199
0
votes
1
answer
46
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
245
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 ...
- 48
0
votes
1
answer
340
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 ...
- 357
1
vote
1
answer
671
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, ...
- 175
0
votes
1
answer
126
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 ...
- 351
0
votes
1
answer
65
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 ...
- 77