23 May 2012

Removing Sitecore Locked Items with SQL

Locks can be a pain especially when using the PageEditor only approach, there are currently no PageEditor visualisation of the Locks on DataSources (Sitecore 6.5 rev. 111230) therefore the user will experience that the page editing is locked on the specific element with the locked data source item, and that the layout is locked for the whole page because of this.

The default Sitecore interface only has a single "my items" button under the review pane that enables a user to see his own locked items and unlock them all, there are default way to find them throughout the site on all users. 

Some pieces of the puzzle are a custom interface to enable unlocking of all locked items (http://blog.wojciech.org/?p=41) and enabling the users themselves to override the locked items (http://briancaos.wordpress.com/2010/09/10/unlock-sitecore-items/) but they do not solve the missing visualisation in the Page Editor issue, there is only a ribbon button on each user that lists all locked elements for his account. Hopefully a visualisation will be added in upcomming Sitecore releases.  In the meantime the solution is to remove the existing locks and make sure new ones don't stay fixed.

I like a direct approach to the problem, and the Sql Query for finding all Sitecore Locked Items is very simple.

SELECT [Id]
      ,[ItemId]
      ,[Language]
      ,[Version]
      ,[FieldId]
      ,[Value]
      ,[Created]
      ,[Updated]
  FROM [Sitecore_master].[dbo].[VersionedFields] 
  where Fieldid like '001DD393-96C5-490B-924A-B0F25CD9EFD8' and Value like '<r owner%'


The SQL Query for Removing the locks is also very simple...

UPDATE [Sitecore_master].[dbo].[VersionedFields] SET VALUE = '<r />' WHERE Fieldid LIKE '001DD393-96C5-490B-924A-B0F25CD9EFD8' AND VALUE LIKE '<r owner%'

Remember backing up and that fiddling in the database removes any business logic rules that might be implemented in the API layer. Its very much at your own risk.

The important settings when battling Locks are found in the web.config and for solving the issue on a long term make sure to enable AutomaticUnlockOnSaved  and disable AutomaticLockOnSave and finally remove the ability for the Lock system to block a user with RequireLockBeforeEditing set to false this way the PageEditor interface will behave as the users expect every time.

<!--  AUTOMATIC LOCK ON SAVE
If true, the a lock is automatically taken on an item
when a user saves the item.
-->
<setting name="AutomaticLockOnSave" value="false" />
<!--  AUTOMATIC UNLOCK ON SAVED
If true, the a saved item is automatically unlocked after
saving.
-->
<setting name="AutomaticUnlockOnSaved" value="true" />
<!--  REQUIRE LOCK BEFORE EDITING
If true, the user must have a lock on a document before
he can edit it, otherwise it is always ready for editing
-->
<setting name="RequireLockBeforeEditing" value="false" />

Looking at the documentation (The content_author's_cookbook) the following is stated:

Sitecore uses item locking to ensure that two different users can’t edit the same item at the same time. If two or more users somehow managed to edit the same item simultaneously only the changes that have been made by the user who pressed Save last will be available. All the other changes will be lost.
Item locking is a system whereby you lock the item you are editing and prevent other users from editing this item until you unlock it again after you have finished editing the item. 
Item locking works differently depending on the tools that you are using.
  • In the Page Editor, you can lock an item before you start to edit it. 
  • In the Content Editor, you must lock an item before you can edit it.

The problem could arise when we use the EditFrame for handling single fields editing, and this opens a hidden lock.

No comments: