Set the transaction isolation level to READ_COMMITTED with READ_COMMITTED_SNAPSHOT turned ON. Yay!
When I mention the words “transaction isolation level” to SQL Server DBAs, the first words out of their mouths are almost always “READ COMMTTED” or “2”. This transaction isolation level makes sense on the surface. You always want your systems to be reading the committed data from the database. If the data is in the middle of being changed, then you might want to wait until the transaction that’s making the change has completed and read the new data. And that’s exactly what READ_COMMITTED gets you. Whenever a transaction is in flight in the database, all of your SELECTs queries that might involve that data will be blocked until that transaction completes. That’s fine if your transactions last for microseconds, but what about transactions that last for several seconds or even minutes?
WOAH! Say what?!?! Coming from the Oracle world, I was honestly shocked when I found this out. I had taken commit and rollback behavior for granted. Even in many highly available configurations, if you block on some key pieces of data for any length of time, then you’re going to be dead in the water. When your database blocks SELECT statements from executing, that’s really bad news. In the case of having something like this occur using Liferay, you will likely experience your connection pools filling up and overflowing which will be immediately followed by hung threads in the app server. End users will likely see the wheels spinning on their browsers as they continually hit F5 in frustration and hope that their Internet connection is just flaky.
This would probably happen during LAR file import or on the target server during Remote Publishing.
Most, if not all, of the transactions that Liferay executes will be short lived with this one exception. The default behavior for a LAR file import or Remote Publish event is to start a transaction, make a ton of changes to content, pages, documents and media, etc., and when it’s all done, commit. That’s exactly what you want to have happen! If something fails during your LAR file import / RP event, you want your database on the target system to remain in a clean state. The last thing you want or need is a partial import. That leads to issues with data integrity, uncertainty about the platform, and an all around feel bad situation for IT and the business.
You can prevent this from happening by changing a small piece of your Liferay configuration and your general SQL Server database configuration.
portal-ext.properties# # Set the definition of the "portal" transaction isolation level. The # "portal" transaction isolation level is not a real isolation level. It is # just a pointer to a real isolation level that can be configured by setting # this property. Set the value to -1 to use the database's default isolation # level. Set the value to 2 to use "read committed". Set the value to 1 to # use "read uncommitted". Set the value to 4 to use "repeatable read". Set # the value to 8 to use "serializable". # #transaction.isolation.portal=2 (Liferay default) transaction.isolation.portal=-1
You will want to add this setting to your portal-ext.properties file. As the comment indicates, you’re delegating control of the transaction out to the database (or datasource as the case may be).
Changes to the database are a little more difficult, but only if you’re not a SQL Server DBA. Expect to get some resistance from the resident SQL Server DBA. Some of the resistance is based on the fact that this change will slightly decrease performance when the database is processing more than a few thousand transactions per second (I Googled for these metrics and actual results may vary). Some of the resistance could come from the fact that they might not really understand the problem domain. Be prepared for “THE BLANK STARE :-O” shortly after you use the word “transaction”.
The SQL Server DBA may want to go do some research. If/when they do, expect them to suggest or ask about using READ_UNCOMMITTED. That is the least amount of work for them and has less potential for causing them issues down the road. You can do this and it works, but there are some cons. If you’re doing a remote publish / LAR file import and your environment is configured with READ_UNCOMMITTED, then you should expect that end users will experience anomalies (e.g. pages missing portlets, permissions not applied to portlet instances, portlets not properly configured, pages missing, themes not applied, etc….) during this process. That may or may not be acceptable in your environment and will likely require the business to make a decision around acceptability.