SQL server supports optimistic concurrency control using row versioning (snapshot isolation and read committed snapshot isolation). These two isolation levels help reducing the query lock contention and works great for certain application types. However one may need to review the usage of these and the application pattern based on the resource consumption in the SQL DB offering for the better database performance.
Please refer to the following page: http://www.sqlindepth.com/row-versioning-in-sql-database-version-v12/