`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' commit ` Another session without waiting get the result: `set transaction isolation level read committed select * from test where id =1000 id | id_int --------------- 1000 |1 ` Could someone explain why I get the result immediately? However, when I use repeatable read isolation level I should wait: `set transaction isolation level repeatable read select * from test where id =1000 `

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 not null drop table #OpenTranStatus CREATE TABLE #OpenTranStatus ( ActiveTransaction varchar(25), Details sql_variant ); -- Execute the command, putting the results in the table. INSERT INTO #OpenTranStatus EXEC ('DBCC OPENTRAN (sqlwatch) with tableresults') SELECT * FROM #OpenTranStatus ``` this is the query it is running (or holding on to): ``` select d.database_id , sd.sqlwatch_database_id, sd.sql_instance into #d from dbo.vw_sqlwatch_sys_databases d inner join [dbo].[sqlwatch_meta_database] sd on sd.[database_name] = d.[name] collate database_default and sd.[database_create_date] = case when d.name = 'tempdb' then '1970-01-01 00:00:00.000' else d.[create_date] end and sd.sql_instance = @sql_instance left join [dbo].[sqlwatch_config_exclude_database] ed on d.[name] like ed.database_name_pattern collate database_default and ed.snapshot_type_id = @snapshot_type_id where ed.snapshot_type_id is null option (keep plan) ``` it has been running for over 60 hours: It has already been killed. so the things I have tried doing: ``` alter database sqlwatch set single_user with rollback immediate ``` but it did not work ``` kill 54 with statusonly ``` SPID 54: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds. question: how can I stop spid54?

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.) and then a set of attributes that corresponds to other columns on the table below. One of this attributes identifies the kind of operation: `Create`, `Update`, `Cancel`. It may happen that I receive a request with many different operations related to a single Year-Sequential key, such as: ``` 2023-001 - Create () 2023-001 - Update () 2023-001 - Cancel () 2023-002 - Create () 2023-002 - Update () 2023-002 - Cancel () etc. ``` Since I'm using a temporal table to store this data to be able to track changes on the rows over time, I've coded the processing method like a single DB transaction with inside a recursive method that process subsets of the items. The recursive method works this way: - It takes the first occurrence of each Year-Sequential key and process it. - If it finds another time the same Year-Sequential key for another operation, it puts it aside for the next round of processing. - When all the "not duplicated key" items have been processed, there's a call to `dbContext.SaveChangesAsync()`. - The processed entities are created and have the `EntityId` value assigned by the database (that I need to use for the next steps of processing). - If the list of "duplicated key items" is not empty, the method recursively calls itself on the duplicates list and starts again from step 1. If instead, the duplicated key items list is empty, the method ends and then there's a call to `transaction.CommitAsync()`, to commit the transaction and save everything to the database. Everything works, but there's a problem with Start/End date used for temporal table period. For each round of the recursive method I correctly get a row created on each single Year-Sequential key. So, taking previous data example, on the History table I've 2 old rows about `2023-001` (Creat

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 would conflict between them - will have the following behaviour: - One of the transaction will insert the row. - The second transaction will wait for the first transaction to commit and then do nothing (or insert in case of rollback of the first transaction). Will the row be locked in the second transaction? Also, I guess the behaviour of the second transaction would be exactly the same than the case where we already have a row (commited), and we do a conflicting `INSERT ... ON CONFLICT DO NOTHING`?

The read committed snapshot and snapshot isolation levels in SQL Server do away with most locking except one: A writer still locks out other writers. The documentation tiptoes around saying as much and subsequently doesn't document anything else which would be very interesting to know: Is it really ever just a modified row that gets exclusively locked? Or can it also be unrelated rows (eg. adjacent in an index) or pages? I did have a look at the locks in `sys.dm_tran_locks` and I only ever saw exclusive locks on modified rows during an uncommitted transaction - pages where merely locked as `IX`. I also tested whether two transactions could modify two different rows simultaneously during two uncommitted transactions in a really small table that likely fits into one page and that worked as well. If indeed only modified rows are exclusively locked, than this would give an application with exclusive access to the database the guarantee of lock-free writing if it makes sure that no two connections write simultaneously to the same row. This would be possible in the scenario I have in mind - but there's hardly a way to do something like that if page locks come into play as it's unpredictable what rows exactly would be affected.

I wonder how databases guarantee that two transactions with different isolation levels run concurrently correctly. That is, different sessions are allowed to use different isolation levels. For example, one session may use "serializable" and the other may use "read committed". How do databases guarantee that two transactions with different isolation levels run concurrently correctly? I tried to google this topic but could not find any material explaining this topic in detail.

Code sample with XACT_ABORT_ON: ``` SET XACT_ABORT_ON; BEGIN TRY BEGIN TRANSACTION //do multiple lines of sql here COMMIT TRANSACTION END TRY BEGIN CATCH IF (@@TRANCOUNT > 0) ROLLBACK; //may be print/log/throw error END CATCH ``` Since XACT ABORT is ON, any error will automatically rollback the transaction. So what purpose does the TRY CATCH block serve?

For the purpose of optimizing SELECT statements I am trying to make an index UNIQUE with the following SQL statement in MySQL: `ALTER TABLE credentials DROP INDEX special_credential_id, ADD UNIQUE KEY special_credential_id(special_credential_id) ` My question is: Is this one transaction? That means if creating the unique index fails, will the old special_credential_id index still be there? Usually it would be easy simply to create a new index but we are talking about a table containing 100 Mio entries.

I am trying to create a deadlock in my postgreSQL-database but the error I'm getting is not really the one I would expect: ``` ERROR: current transaction is aborted, commands ignored until end of transaction block SQL state: 25P02 ``` My setup is basically a mix of these two: - https://stackoverflow.com/a/22776994/2516892 - https://www.cybertec-postgresql.com/en/postgresql-understanding-deadlocks/ I opened two browser windows of pgAdmin 4 and have two query windows. One with the following: ``` BEGIN; UPDATE "Products" SET "Price" = "Price" * 0.03 WHERE "PID" = 1 RETURNING *; UPDATE "Products" SET "Price" = "Price" * 0.03 WHERE "PID" = 2 RETURNING *; COMMIT; ``` In the other is use this one: ``` BEGIN; UPDATE "Products" SET "Price" = "Price" * 3 WHERE "PID" = 2 RETURNING *; UPDATE "Products" SET "Price" = "Price" * 0.03 WHERE "PID" = 1 RETURNING *; COMMIT; ``` My intention was to execute both as close to each other as possible but I got that error. Strangely even when I just try to run them single the same error persists. I can't make much of that error code. What could it be? The table itself is as basic as it gets: ``` CREATE TABLE IF NOT EXISTS "Products" ( "PID" integer NOT NULL, "Name" character varying(255), "Price" double precision, "Stock" integer, CONSTRAINT "Products_pkey" PRIMARY KEY ("PID") ) ``` The answers I found online are not particularly helpful. Like this one from here: ``` This log event happens when a transaction fails due to a potentially unrelated error, and you try to run another query in the failed transaction. ``` So I have tried to simply do : ``` BEGIN; Select "Price" from "Products" COMMIT; ``` And receive the same error. Without the transaction block it works

How to use a transaction-based strategy to ensure that the same location cannot be booked more than once on the same day? It was suggested to me that it will different for every isolation level. Could you add an example for every one of them? (`read committed`, `repeatable read` and `serializable`). I would like to understand every one of them. Here are tables and test data: ``` CREATE TABLE place ( place_id INT PRIMARY KEY, Name CHARACTER VARYING(50) NOT NULL, Type CHARACTER VARYING(50) NOT NULL ); CREATE TABLE visit ( visit_id SERIAL PRIMARY KEY, place_id INT NOT NULL, place_dt TIMESTAMP NOT NULL, FOREIGN KEY (place_id) REFERENCES place(place_id) ); INSERT INTO place(place_id, Name, Type ) VALUES (1, 'Denali', 'mountain'), (2, 'Brindley', 'mountain'), (3, 'St. Louis Cathedral', 'church') ; INSERT INTO visit(place_id, place_dt ) VALUES (1, '2019-01-02 10:00'), (2, '2019-01-02 11:00'), (3, '2019-01-03 14:09') ; ``` fiddle What I want is that for each isolation level, look for a transaction-based strategy to ensure that the same location cannot be booked more than once on the same day. Given that it could be the case where a strategy could be optimal for various levels of isolation.