New answers tagged transaction
0
SELECT FOR UPDATE can't lock rows that don't exist, therefore if it is used on table seats, it cannot prevent the insertion of new rows. So if you use that, it has to be done on table lifeboats.
Since customers will be in a hurry to find a lifeboat with available seats, it would be better to let go of the slow count() and add a column occupied_seats in table ...
0
I would argue that transaction control does not belong to stored procedures at all, apart from some very particular circumstances (e.g. where you want to log audit records regardless of what happens to the "outer" transaction -- i.e. "autonomous transaction"). The procedure has no way of knowing if it is part of a larger unit of work and ...
2
If you are using a recent version of SQL Server you can compare session waits per sys.dm_exec_session_wait_stats in the small batch vs one delete comparison. Enabling Query Store and comparing metrics such as logical reads, physical reads, and CPU time among the small batch deletes as well as to the single delete will also be instructive.
Without additional ...
2
As with most things, it depends...
The longer a transaction is open against a set of Tables, the longer those Tables are locked for. Locking on a Table causes contention against other queries trying to read from and write to that Table. So by breaking up a transaction into multiple smaller transactions reduces the continuous runtime that those Tables are ...
0
Why not stick with the default transaction isolation level of read committed which everyone expects and understands best?
Use optimistic locking, for example
update <table>
set <column> = <new value>
where id = <id>
and <column> = <old value>
If that update fails, it means someone else got there before you and you ...
0
REPEATABLE READ is sufficient for your case. It will by definition prevent a “lost update”.
SERIALIZABLE is quite a bit more expensive than REPEATABLE READ, which is the “cheapest” of all isolation levels. More locks will be taken (SI locks that don't block anything, but can cause a transaction to abort), and these locks have to survive a commit. It is ...
1
This depends entirely on how you setup the transaction.
If you have START TRANSACTION and COMMIT; surrounding a series of INSERT, UPDATE, and DELETE queries, there are some commands that trigger an implicit commit:
ALTER TABLE
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
LOCK TABLES
UNLOCK TABLES
SET AUTOCOMMIT = 1
BEGIN (can ...
0
new try with temporary AND temporal table:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION;
DECLARE @SYSUTCDATETIME_1st datetime2(7) = SYSUTCDATETIME();
WAITFOR DELAY '00:00:00.100';
DECLARE @SYSUTCDATETIME_2nd datetime2(7) = SYSUTCDATETIME();
CREATE TABLE #SYSUTCTRANSBEGINTIME
(PK bit
, SysStartTime datetime2(7) GENERATED ...
0
that does not require me to write to and select from a system versioned table first?
As you are already writing to at least on versioned table in the transaction, you can use the OUTPUT clause to record SysStartTime (or other name if you are not using the common one) from there like so:
DECLARE @StampsOfTime TABLE (ts DATETIME2)
INSERT Testo (SomeField) ...
2
A solution could be to build a simple SSIS package that runs the stored procedure you need; then call it from tsql in this way:
declare @execution_id bigint
exec ssisdb.catalog.create_execution
@folder_name = 'folder'
,@project_name = 'project_name'
,@package_name = 'run_sp.dtsx'
,@execution_id = @execution_id output
exec ssisdb.catalog....
1
Are you asking of you can call some other stored procedure asynchronously? No, not natively, so to speak. We usually have two options for this:
Execute an Agent job, using sp_start_job. Note that an agent job doesn't have parameters so if you want to pass data into this job, so to speak, you can use a table as a communication mechanism. Note that if the ETL ...
-1
declare @TRANSACTION_BEGIN_UTC datetime2(7); set @TRANSACTION_BEGIN_UTC = SYSUTCDATETIME()
but MUST be saved in Variable at BEGINNING because SYSUTCDATETIME() changes during Transaction
Feb.1 2021: NOT WORKING! : found Example where SysStartTime is earlier:
SYSUTCDATETIME() = 2021-02-01 06:54:58.2040041
SysStartTime = 2021-02-01 06:54:58.2030165
1
The formula is correct.
The number of live rows is taken from the reltuples column in pg_class, and the result of the formula is compared to n_mod_since_analyze from pg_stat_all_tables.
Note that autovacuum_analyze_scale_factor and autovacuum_analyze_threshold can be overridden by storage parameters on th table.
If all available autovacuum workers are ...
Top 50 recent answers are included
