Locking Rows in SQL Server 2005
There turns out to be a few different ways to lock rows in SQL Server 2005.
The `SERIALIZABLE` tag is one of the options. Here are a few more:
SQL Server Row Locking Strategies
As an aside, Rails (and Hibernate) support object locking with database locking semantics. In Rails 1.1.x, only optimistic locking was supported. Rails 1.2 added support for pesimistic locking.
Hibernate accomplishes the row lock with:
return tableName + ” with (updlock, rowlock)”;
AFAICT, Rails punts on this. I can’t find the locking syntax in the connection adapter. There is an add_lock! But it expects the locking syntax string unless you can use the default (`FOR UPDATE`, which of course SQL Server doesn’t support). That’s pretty lame on Rails’ part. But anyway…
October 30th, 2007 at 2:42 am
A possible patch to provide locking support is to add to the sqlserver adapter (above the private section):
def add_lock!(sql, options)
lockStr =
case lock = options[:lock]
when true: ‘WITH (UPDLOCK ROWLOCK)’
when String: lock
end
return unless lockStr
sql.sub!(/( LEFT OUTER JOIN | LEFT JOIN | RIGHT OUTER JOIN | RIGHT JOIN | INNER JOIN | JOIN | WHERE | ORDER | GROUP |$)/i, ” #{lockStr}\\1″)
end
Hopefully someone with a little more ruby experience than I can make it look cleaner.