New answers tagged transaction
6
@@TRANCOUNT reports a count of BEGIN TRANSACTION statements, not active transactions. From a different perspective, it is reporting the depth of a nested transaction.
@@TRANCOUNT (Transact-SQL) Returns the number of BEGIN TRANSACTION
statements that have occurred on the current connection. [source]
Misunderstandings of nested transactions and ...
1
This behaviour is described in the Microsoft documentation on ROLLBACK TRANSACTION:
ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.
Because a ROLLBACK TRAN can exist in the middle of some complex logic, parts of your code might not even be sure whether one was executed or not, making it difficult to figure out whether you need to ...
1
You could use a try ... catch block:
-- Create test table
if object_id('YourTable') is not null
drop table YourTable
create table YourTable (id int identity);
-- Safeguarded insert
set identity_insert YourTable on;
begin try
insert YourTable (id) values (100);
-- this generates an exception
declare @oops int = cast('a' as int)
end try
...
1
I believe your best bet is going to be to use a TRY-CATCH. I've done two things here. First declare and get your "oldID" before your transaction begins. This will help to keep your transaction time down and avoid deadlocks as much as possible. Second I've put the "core" of the rest of it into a TRY block and then in the CATCH block put the reseed again. ...
1
Please look carefully at the queries that are locking
The SHOW ENGINE INNODB STATUS\G is incomplete
I cannot fully tell you anything more because you gave me three transactions that
execute the exact same query
lock the same page in the GEN_CLUST_INDEX
as shown from your display
---TRANSACTION 0 2491310, ACTIVE 29 sec, process no 6622, OS thread id ...
2
From comments, I'm guessing you had a client side Command timeout that has aborted the SQL query. This does not rollback the transaction because the connection stays open on SQL Server due to connection pooling.
So, you need to use SET XACT_ABORT ON or add some client rollback code
See SQL Server Transaction Timeout for all the gory details
3
Use the most_recent_sql_handle in sys.dm_exec_connections to see the last statement that was executed.
SELECT t.text,
QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid)) + '.'
+ QUOTENAME(OBJECT_NAME(t.objectid, t.dbid)) proc_name,
c.connect_time,
s.last_request_start_time,
s.last_request_end_time,
s.status
...
3
Have you tried using Adam Machanic's sp_whoisactive? There's an option to get the outer command to see if it really is within a proc. It could be the application is holding open a transaction instead of committing it. Try looking at DBCC OPENTRAN as well.
4
The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:
CREATE TABLE table_to_be_updated (
id serial PRIMARY KEY,
other_column text,
column_changing text
);
INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', ...
1
It does. The first sentence you marked in boldface relate to "concurrent transactions," i.e., transactions that change data but that are not committed while your transaction is still going on.
If these concurrent transactions commit before yours, then you see their data in your transaction in successive select.
Top 50 recent answers are included
