234 questions
-1
votes
1
answer
114
views
Dirty reads happening in RCSI in Azure SQL Server
Although my database isolation level is set to READ_COMMITTED_SNAPSHOT, I am still getting dirty (uncommited) reads.
SELECT COUNT([random_string]) NoTx FROM [dbo].[IncTesting5];
BEGIN TRANSACTION;
...
- 95
0
votes
0
answers
48
views
Concurrent transactions containing HOLDLOCK on SELECT
I'm just trying to understand a bit more about the behaviour of HOLDLOCK in the scenario where multiple transactions are running concurrently.
The transaction is basically doing the following:
Start ...
- 35
0
votes
0
answers
141
views
How to save all changes in one transaction while using MediatR
I'm using MediatR to create an order with two types of tasks. Each task has its own command, and I would like to use a transaction to ensure that both types of tasks are saved, or none are saved.
I ...
- 55
-1
votes
1
answer
454
views
SQL Transaction using Apache Spark and Scala
With a data frame of IPs and updated status, we first need to delete the data frame's IPs from the SQL table. After that, we should insert this data frame with the updated status using Spark SQL. ...
1
vote
0
answers
196
views
What hanging transaction in tempdb mean?
I'm trying to troubleshoot what is causing timeouts when querying specific table in production.
I suspected that there are some uncommitted transactions, so I ran following query:
SELECT trans....
- 27.8k
0
votes
0
answers
54
views
Can you have a Select in the middle of an ADO transaction
I have some code that goes like this:
SqlTransaction trans = conn.BeginTransaction("MyTransfer");
cmdStep1.ExecuteScalar();
cmdStep2.ExecuteScalar();
cmdStep3.ExecuteScalar();
...
1
vote
1
answer
246
views
How does SQL Server handle different type of errors?
Please find the below sample code
drop table if exists #myTest
create table #myTest (
id int,
name varchar(30)
)
insert into #myTest values
(1, 'John')...
- 724
0
votes
1
answer
1k
views
SQL Server deadlock on read uncommitted isolation level
I get error message "Transaction (Process ID 60) was deadlocked on lock resources with another process ...". I have two simple queries like:
Query 1:
BEGIN try
SET TRANSACTION ISOLATION ...
0
votes
0
answers
552
views
Snowflake | retain stream data in case of failure
We want to use a stream to insert data from a source table to multiple target tables. For that purpose, we have created steam on the source table.
We are using a Java script procedure for this. We are ...
- 1,776
0
votes
1
answer
670
views
When are go goose transactions committed?
I have some code that needs to be run in a Tx like here
I generated a go sql migration as mentioned here in the official docs for go goose
my questions are:
when are these transactions actually ...
- 714
1
vote
1
answer
71
views
Problem with creating mysql event. Start transaction error [duplicate]
I what to create an event that delete rows fom a table that are older than 15 minus and add their value back to other table. In the code below mysql said that I have a syntax error on line 5
CREATE ...
- 25
0
votes
1
answer
397
views
How to make MySQL's start transaction query fail?
I can see in the PHP documentation that begin_transaction can fail. How can I make it fail? (All of my tables use the InnoDB engine. MySQL version 5.7.38-log.)
Why do I care? I'm investigating a ...
- 11
1
vote
1
answer
1k
views
How to perform transaction operation in Room database to delete some tables android
I am not familiar with Room Database. I want to clear the data from some tables when I click on button. I have the similar code in IOS but I am not sure where I have to write this method and call ...
- 2,326
1
vote
2
answers
2k
views
Snowflake update statements locks the entire table and queues other update statements
We have a use case where we need to execute multiple update statements(each updating different set of rows) on the same table in snowflake without any latency caused by queuing time of the update ...
4
votes
0
answers
404
views
.NET Core EF Use Same Transaction over multiple services throws cannot issue save transaction when there is no active transaction
In the .NET core web API application we are using MS SQL transactions in one service method and in the middle of that method, we are calling another service method, inside this method when trying to ...
- 51
1
vote
1
answer
65
views
Is transaction isolation unnecessary in MYSQL when configuring one buffer pool?
According to https://dev.mysql.com/doc/refman/8.0/en/innodb-multiple-buffer-pools.html, buffer pool was protected by its own buffer pool mutex. So there is only one data request can visit buffer pool ...
- 89
0
votes
1
answer
1k
views
Query Error Handling in MySQL Transaction Statement
I am taking a Database Design & Development course, and I just learned about SQL transactions. The textbook (Learning SQL 3rd Edition by Alan Beaulieu) is really good at explaining how things work,...
- 528
3
votes
2
answers
8k
views
Node.js MS SQL transaction
Can anyone help to implement MS SQL transactions in Node.js . I am try to execute multiple stored procedures inside a promise.
Method 1
const executeProcedure = async (data1, data2) => {
try {
...
- 43
1
vote
1
answer
719
views
encapsulating a Drop and Add constraint in a transaction
I have a simple drop and add a constraint encapsulated in a transaction as follows:
begin;
alter table movies drop constraint actor_check;
alter table movies add constraint actor_check check ( "...
- 4,551
0
votes
2
answers
1k
views
Is it necesserly to use sqltransaction with using keyword in C#
I have following with two using block:
using (SqlConnection connection = new SqlConnection(_connectionString))
{
String query = "query...";
using (SqlCommand command = new SqlCommand(...
- 1,640
-2
votes
1
answer
46
views
Sql transaction what can come after
Can you please tell me why c and d are the right answers here ?
Thank you
Select the statements that can be used to complete the following SQL code block
BEGIN TRANSACTION
SELECT COUNT(*) FROM ...
- 77
1
vote
1
answer
168
views
LINQ to SQL not creating Transactions
I've been searching everywhere, to try and get over this issue but I just can't figure this out.
I'm trying to make many changes to the DB with one single transaction using LINQ to SQL.
I've created a ...
0
votes
2
answers
835
views
Does INSERT INTO with Subqueries run as a single transaction (thread safe)?
I have an insert query with subqueries which i want to run on a Postgres database. But i'm not sure if the query with its subqueries run in a single transaction.
We have two entities 'configs' and '...
0
votes
1
answer
58
views
How to write a Mysql transaction within a static JS-method?
I have this static method that executes a SQL statement:
static save(habit){
return db.execute(
'INSERT INTO habits (name, repeatVal, notification, color, question, id) VALUES (?, ?, ?, ?, ...
- 49
1
vote
1
answer
1k
views
PostgreSQL concurrency in transactions
I want to understand how transactions work in SQL, specifically in PostgreSQL
Imagine I have a very large table (first_table) and the query below lasts 2 seconds and I execute the query below via psql....
- 450
0
votes
0
answers
502
views
Are newly inserted records available to subsequent SQL commands within the same transaction?
Let's say I have some C# code that executes two SQL commands:
var MyTransaction = DbConnection.BeginTransaction();
//using MyTransaction: INSERT INTO TABLE_1 (ID, SOURCE_FIELD) VALUES (99, 'HELLO')
//...
- 4,046
1
vote
1
answer
239
views
How many transactions were started during the execution of the request?
I have a transaction. The code is
set implicit_transactions on
select getdate()
begin transaction
begin transaction
select * from price p1, product p2 where p1.product_id = p2.product_id
rollback
...
- 13
0
votes
2
answers
35
views
Group by and filter on 2 Distinct values in SQL
I have table T1
ID Size
A 1
A 2
A 3
B 3
B 4
C 2
C 4
I want to group by ID and filter the smallest size for each ID
Desired outcome:
A 1
B 3
C 2
I tried doing something like this:
SELECT ID, ...
- 3
0
votes
0
answers
427
views
EntityFrameworkCore swap DbConnection (add Transaction)
I have large DB model (hundred of tables) split to multiple EntityFrameworkCore DbContexts. Is is a quite common use case when I modify multiple entities in two (or more) different DbContexts, but I ...
- 2,158
0
votes
0
answers
192
views
T-SQL insert into sometimes fails to add to a table at random
So I have two tables, Records (Input ID = Primary Key) and TaskNotes (Input Id, TaskNote : No primary key).
There used to be a single stored procedure which would add to the record table, get the ...
- 33
-1
votes
1
answer
110
views
Problem in reading the database collation into a variable in SQL Server
I've faced this strange thing that I can't set the value of a variable to a result of a query. The query is reading the collation of a database and works fine by itself:
SELECT collation_name
FROM ...
- 389
1
vote
2
answers
3k
views
SQL Server Partial commit in transaction
I have a transaction and two tables where i am inserting some data, can I do partial commits in SQL server
BEGIN TRANSACTION tran1
BEGIN TRY
--Insert into Table1
--Insert into Table2
COMMIT ...
- 1,460
1
vote
2
answers
868
views
Why is failed transaction not rolling back with TransactionScope?
What I'm trying to achieve is:
I'm calling stored procedures from 2 different databases. SP's have a simple insert entry in a table. There is no problem if both the transactions are successful, But ...
- 241
6
votes
2
answers
2k
views
SQL Server deadlock when only inserting new rows without performing any selects
I am seeing constant deadlocks in my app, even though it performs no select statements, no delete statements, and no update statements. It is only inserting completely new data.
TL;DR: It seems to be ...
- 23.6k
0
votes
1
answer
1k
views
How to pass Dapper TransactionScope across multiple method
Say I have 2 methods (A and B)
public void A()
{
using (var connection = Database.GetConnectionString())
using (var tran = new TransactionScope())
{
//do "A"
tran....
- 3,485
0
votes
1
answer
221
views
What are the full requirments for a SQL Server Transaction to run?
As the title says, what are the full requirements to get a SQL transaction running with SQL Server? The transaction will eventually get more complex, but so far the debugging process has left me with ...
- 760
-1
votes
1
answer
150
views
PowerShell to MySQL : SQL statement after the COMMIT is also committed
I am inserting values from PowerShell into MySQL table. I have 2 Insert statements (insertQuery1,insertQuery2) in transaction.
There is COMMIT after insertQuery1 so changes in query_1 is committed but ...
- 783
0
votes
1
answer
393
views
ASP.net core 2. 0 get error when use transaction scope : The requested operation cannot be completed because the connection has been broken
I have created an API to add multiple items. I use transaction scope because I don't have to insert all items if found a problem in any item. so I have used TransactionScope so doing dispose of the ...
- 705
-1
votes
1
answer
171
views
Is mixing transactions and table locks unrealistic in MySQL 5.7?
I am looking to mix table locks and transactions in an API. I have selected MySQL 5.7 as the storage engine, though I am not particularly committed to any specific RDBMS. I am interested in seeing ...
- 20.2k
0
votes
0
answers
317
views
How to Insert records in multiple inter dependent tables in a Single SQL Transaction?
I have a scenario where i need to perform INSERT Operation on multiple SQL tables, There is a dependency for inserting records in each table.
I want to perform first INSERT to Deal table and then ...
- 922
8
votes
5
answers
21k
views
SQLite error: cannot start a transaction within a transaction with very basic tables
I am brand new to SQL, and I am learning on an SQLite editor. So I create a couple of very simple tables. This code is straight from Linkedin learning "SQL essential training", and I am using the ...
- 431
-1
votes
2
answers
744
views
SQL: Is it possible to insert into two tables at the same time with autogenerated id output from first table into second
My database contains two tables FileStore and FileRepository.
The FileStore table contains 3 columns Id (autogenerated uniqueidentifier), FileName and Description
-> In Initial state with demo data
...
- 561
1
vote
0
answers
33
views
Using DELETE with OUTPUT INTO clause not behaving atomic with SqlTransaction
As far as I read, this statement is atomic. Either both will fail or both will succeed:
DELETE FROM TABLE1
OUTPUT DELETED.*
INTO TABLE2
WHERE <condition>
I have these statements executed ...
- 11
1
vote
1
answer
1k
views
Transaction rollback VS Delete Records
Looking for some insights on using Transaction or Delete queries when the subsequent request fails. In brief, in my application, I'm inserting into two tables by calling two stored procedures and the ...
- 2,131
2
votes
3
answers
12k
views
After truncate , we can not rollback data in SQL? [duplicate]
I have a table named a that has 26 rows:
Select * from a
Output is 26
Begin Tran
Truncate table a
Select * from a
Output is zero rows
Rollback Tran
Select * from a
Output is again 26 rows
Truncate ...
- 347
0
votes
1
answer
55
views
Interesting logic to be solved in teradata SQL
In the image the target is top calculate years since last DX.
A year level we want to calculate thw number of years since we have found a DX.
Eg
There was a dx for id 1 in 2014 so in 2015,2016 and ...
- 1
3
votes
1
answer
231
views
SQL Server - Registering user code to be run when a transaction completion is attempted
I have an SQL CLR Stored Procedure which registers an event handler for the completion of the current transaction
[SqlProcedure]
public static void MySProc()
{
Transaction.Current....
- 2,986
6
votes
1
answer
2k
views
How to achieve consistent read across multiple SELECT using AWS RDS DataService (Aurora Serverless)
I'm not sure how to achieve consistent read across multiple SELECT queries.
I need to run several SELECT queries and to make sure that between them, no UPDATE, DELETE or CREATE has altered the ...
- 31.3k
0
votes
0
answers
382
views
The INSERT statement conflicted with the FOREIGN KEY constraint "tableConstraint"
Full error: .Net SqlClient Data ProviderThe INSERT statement conflicted with the FOREIGN KEY constraint "FK_SalesOrder_SalesOrderHead". The conflict occurred in database "ControlSecurity", table "dbo....
- 503
0
votes
0
answers
227
views
check the manual that corresponds to your MySQL server version for the right syntax to use near ''execute_transaction'(IN sql_text VARCHAR(8000))
DELIMITER //
CREATE PROCEDURE 'execute_transaction'(IN sql_text VARCHAR(8000))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
Rollback;
SELECT CONCAT(sql_text, 'is not ...
- 432