I have a table that is used to persist messages/events which means data is inserted and deleted over and over and hardly ever read and usually there is limited amount of data in it... since a row is inserted... the event processed and then the row is deleted.
The data in it only lives a short time.
I am getting deadlocks on that table trying to delete by the primary key. How can I reduce the locking on this table and prevent deadlocks? (delete from ops_event where "pkid" = ?)
Is there anything like the DB2 "Volatile" keyword for the table?
First, please use a low transaction isolation level if it is allowed, for example: READ UNCOMMITTED.
Second, please specify “with(ROWLOCK)” option with the insert, update commands. A row lock is as fine as you can get in terms of granularity, and would allow other processes to update the table at the same time.
For more detail information, you can refer to the following links:
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.