Stack Overflow
Stack Overflow

Questions tagged [sqltransaction]

An SQL-transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order. Executing SQL-statements in Transactions, can ensure data integrity and handle database errors.

An SQL-transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order.

A transaction is the propagation of one or more changes to the database. For example, creating, updating, or deleting a record from the table. Transactions are used to ensure data integrity and to handle database errors, by collecting SQL queries into a group and executing all of them together, as one unit of work.

Properties of Transactions: Transactions have the following four standard properties, usually referred to by the acronym ACID:

  • Atomicity: ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.
  • Consistency: ensures that the database properly changes states upon a successfully committed transaction.
  • Isolation: enables transactions to operate independently of and transparent to each other.
  • Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.
233 questions
0
votes
0 answers

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') //using MyTransaction: UPDATE TABLE_2 SET…
user3163495
  • 2,425
  • 2
  • 26
  • 43
0
votes
2 answers

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, Size FROM T1 WHERE ID IN (SELECT DISTINCT ID FROM…
J D
  • 3
  • 2
0
votes
0 answers

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 need to commit this operations withing a single…
Luke1988
  • 1,850
  • 2
  • 24
  • 42
0
votes
0 answers

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 primary id that was generated, then add that ID to…
Tiernan
  • 33
  • 5
0
votes
2 answers

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 TRANSACTION tran1 …
VR1256
  • 1,266
  • 4
  • 28
  • 56
0
votes
1 answer

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.Complete() } } public void B() { using (var connection =…
tickwave
  • 3,335
  • 6
  • 41
  • 82
0
votes
1 answer

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 stripping out all the logic (and nearly tearing…
Joe
  • 678
  • 9
  • 24
0
votes
1 answer

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 transaction if found any validation failed in an…
Abhijit Pandya
  • 705
  • 2
  • 12
  • 33
0
votes
0 answers

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 list of contacts and companies to the respective…
Rahul Hendawe
  • 902
  • 1
  • 14
  • 39
0
votes
1 answer

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 2017 we are populating the value in incremental…
Nirvana
  • 1
0
votes
0 answers

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.SalesOrderHead", column 'id'. DB Table…
awaisharoon
  • 463
  • 1
  • 3
  • 16
0
votes
0 answers

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 valid'); END; SET autocommit = 0; SET…
Miladfa7
  • 102
  • 1
  • 7
0
votes
2 answers

PostgreSQL 9.3 idle in transaction

In our product sometimes there is a situation when a database is locked with an idle in transaction connection. I am using postgresql-9.3(9.4), java connection via jdbc driver. All connections are set to be autocommit. The problem can only be solved…
Alex
  • 706
  • 7
  • 16
0
votes
0 answers

Avoid deadlocking within a serializable isolation-level transaction?

I am trying to implement an event source in SQL Server, and have been experiencing deadlocking issues. In my design, events are grouped by DatasetId and each event is written with a SequenceId, with the requirement that, for a given DatasetId,…
Alex Dawes
  • 1
  • 1
0
votes
0 answers

Force Commit in SQL Procedure for a Transaction initiated from Java Spring

I have a Spring batch which invokes a SQL Procedure. SQL Procedure is to extract a file (using BCP command). Internally code of SQL proc fetches the data from few tables and creates a string which it inserts in one Temp table and then there is BCP…
vnkotak
  • 129
  • 4
  • 14
1 2 3
…
15 16