SQL Server locking is much different than Access locking. SQL Server can apply locks at many different levels; row, pages, keys, indexes, tables, and even databases.
You can configure these, and your best bet is to look in the SQL Server Book Online (BOL) and read up on locking. Otherwise, this would be a really long post. :-)