Nope.
Why would you want to?
What is it you're trying to address?
|||i have a transaction that backs up databases and stores results about the backup in a table, ie if it was a success or if it failed. It is causing a deadlock situation when it more that one database is being backed up at one time, ie two or more instances of the transaction running at the same time, backing up different databases|||
That in itself doesn't sound like it should cause deadlocks.
What else is going on in your procedure?
Are you using explicit transactions?
Post some code if you could.
|||i have isolated the problem, when the table is updated, it is causing a lock. When the other transactions are ran, they have to waite until the first transaction has finished until it has access to the table. I am using row lock, as i thought this would only lock the row im am updating, not the table, but it is not workinghere is the code for the update
update database_backup_scheudle with (rowlock)
set BackUpLocation = @.sDatabaseBackup_LiveServer_UNC_Location,
DatabaseName = @.sDatabaseName_To_BackUp,
BackUpName = @.sDatabaseBackup_Name,
BackUpType = @.sDatabaseBackup_Type,
BackUpNumber = @.iBackUpNumNew,
BackUpDate = GETDATE(),
BackUpDay = @.sDay,
BackUpMonthName = @.sMonthName,
BackUpYear = @.sYear,
BackUpTime = @.sTime,
BackUpTimePeriod = @.sTimePeriod
WHERE BackUpID = @.iBackUpID
|||
Hello,
You shouldn't need the hint.
Do you have a clustered index on BackupID?
if not create one and it should solve your problem.
if you have a clustered index on other column you should atleast have a index on BackupID.
/P
|||Are you updating before the backup or after?
|||Are you getting a deadlock or simply blocking? Deadlock (except in cases where there is bug in SQL Server) happens due to incorrect application logic and transaction semantics. You don't need the rowlock hint in the update statement. SQL Server defaults to row lock and escalates to table only if needed.|||problem solved! there was another update into the table futher on in the transaction, which was causing another lock! Thanks for your help