This one will take some explaining. What I've done is create a specific custom message queue in SQL Server 2005. I have a table with messages that contain timestamps for both acknowledgment and completion. The stored procedure that callers execute to obtain the next message in their queue also acknowledges the message. So far so good. Well, if the system is experiencing a massive amount of transactions (thousands per minute), isn't it possible for a message to be acknowledged by another execution of the stored procedure while another is prepared to so itself? Let me help by showing my SQL code in the stored proc:
--Grab the next message id
declare @MessageId uniqueidentifier
set @MessageId = (select top(1) ActionMessageId from UnacknowledgedDemands);
--Acknowledge the message
update ActionMessages
set AcknowledgedTime = getdate()
where ActionMessageId = @MessageId
--Select the entire message
...
...
In the above code, couldn't another stored procedure running at the same time obtain the same id and attempt to acknowledge it at the same time? Could I (or should I) implement some sort of locking to prevent another stored proc from acknowledging messages that another stored proc is querying?
Wow, did any of this even make sense? It's a bit difficult to put to words...
A database lock is used to “lock” some data in a database so that only one database user/session may update that particular data. So, database locks exist to prevent two or more database users from updating the same exact piece of data at the same exact time.
The LOCK TABLE statement allows you to explicitly acquire a shared or exclusive table lock on the specified table. The table lock lasts until the end of the current transaction. To lock a table, you must either be the database owner or the table owner.
Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by different transactions. For example, if an exclusive (X) lock is held on a row within a table by a transaction, no other transaction can modify that row until the lock is released.
LOCK TABLES implicitly commits the active transaction, if any. Also, starting a transaction always releases all table locks acquired with LOCK TABLES. This means that there is no way to have table locks and an active transaction at the same time. The only exceptions are the transactions in autocommit mode.
Something like this
--Grab the next message id
begin tran
declare @MessageId uniqueidentifier
select top 1 @MessageId = ActionMessageId from UnacknowledgedDemands with(holdlock, updlock);
--Acknowledge the message
update ActionMessages
set AcknowledgedTime = getdate()
where ActionMessageId = @MessageId
-- some error checking
commit tran
--Select the entire message
...
...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With